/* Threat_grid Subnational missing_higher_level_threat.sql Finds element records with one or more cases of lower level threat exists in the threats grid without the corresponding higher level threat NOTE IF you want to return all threat grid rows for these elements remove all the -- which comment out lines of the query then the query results will include ALL of the rows in the threats grid for the elements. (Not just those rows where a corresponding higher level row is missing) */ SELECT est.element_subnational_id , eg.elcode_bcd , (select categ.name_category_desc from d_name_category categ where categ.d_name_category_id = sn.d_name_category_id) category , sn.scientific_name sNAME , nation.iso_nation_cd natn , subnation.subnation_code subn -- , d_threat_cat.DISPLAY_VALUE IUCN_THREAT_CATEGORY -- , d_threat_cat.d_iucn_threat_category_id -- , d_threat_cat.display_order d_iucn_threat_display_order , sn.d_name_category_id FROM scientific_name sn , element_subnational est , element_national en , element_global eg -- , el_subnatl_threats_assess threats -- , d_iucn_threat_category d_threat_cat , nation , subnation WHERE est.sname_id = sn.scientific_name_id and eg.element_global_id = en.element_global_id and en.element_national_id = est.element_national_id -- and est.ELEMENT_subnational_ID = threats.element_subnational_id and en.nation_id = nation.nation_id and est.subnation_id = subnation.subnation_id -- and threats.d_iucn_threat_category_id = d_threat_cat.D_IUCN_THREAT_CATEGORY_ID and eg.inactive_ind = 'N' and ( (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (2,3,4) )/* threat_cat_desc - 1 - Residential and commercial*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 1)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (6, 7, 8, 9, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104)) /* threat_cat 2- Agriculture and Aquaculture */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 5)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (52,53,54,55,91, 92, 93, 94)) /* threat_cat 2.1 - Annual and perennial non-timber crops*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 6)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (56,57,58,59, 95, 96, 97)) /* threat_cat 2.2 - Wood and Pulp Plantations*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 7)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (60,61,62,63,64,65, 98, 99, 100, 101)) /* threat_cat 2.3 Livestock farming and ranching*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 8)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (102, 103, 104)) /* threat_cat 2.4 Marine and freshwater aquaculture*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 9)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (11,12,13)) /* threat_cat 3-Energy Production and Mining*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 10)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (15,16,17,18)) /* threat_cat 4-Transportation and Service Corridors */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 14)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (20,21,22,23, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123)) /* threat_cat 5-Biological Resource Use */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 19)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (105, 106, 107, 108)) /* threat_cat 5.1 - Hunting and collecting terrestrial animals */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 20)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (109, 110, 111, 112)) /* threat_cat 5.2 - Gathering terrestrial plants */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 21)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (113, 114, 115, 116, 117)) /* threat_cat 5.3 - Logging and wood harvesting */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 22)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (118, 119, 120, 121, 122, 123)) /* threat_cat 5.4 - Fishing and harvesting aquatic resources */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 23)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (25,26,27)) /* threat_cat 6-Human instrusions and disturbance */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 24)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (29,30,31, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137)) /* threat_cat 7-Natural Systems Modifications */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 28)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (124, 125, 126)) /* threat_cat 7.1 - Fire and fire suppression*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 29)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (66,67,68,69,70,71,72,73,74,75,76,77,78, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137)) /* threat_cat 7.2-Dams and Water Management/use */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 30)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (67,68,69,70,71,72,73)) /* threat_cat 7.2.1-Agriculture*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 66)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (33,34,35, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148)) /* threat_cat 8-Invasive and other problematic species and genes */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 32)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (138, 139)) /* threat_cat 8.1 - Invasive non-native/alien species/diseases*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 33)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (140, 141)) /* threat_cat 8.2 - Problematic native species/diseases*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 34)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (143, 144)) /* threat_cat 8.4 - Problematic species/diseases of unknown origin*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 142)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (146, 147)) /* threat_cat 8.5 - Viral/prion-induced diseases*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 145)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (37,38,39,40,41,42, 79, 82, 83, 84, 85, 86, 87, 88, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166)) /* threat_cat 9-Pollution */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 36)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (149, 150, 151)) /* threat_cat 9.1 - Domestic and urban waste water */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 37)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (152, 153, 154)) /* threat_cat 9.2 - Industrial and military effluents */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 38)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (79,82,83,84,85, 155, 156, 157, 158)) /* threat_cat 9.3 Agriculture and forestry effluents */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 39)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (86,87,88, 159, 160, 161, 162)) /* threat_cat 9.5 Air-borne pollutants */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 41)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (163, 164, 165, 166)) /* threat_cat 9.6 - Excess energy*/ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 42)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (44,45,46)) /* threat_cat 10-Geologic Events */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 43)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (48,49,50,51,167)) /* threat_cat 11-Climate change and severe weather */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 47)) OR (est.element_subnational_id in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id in (169)) /* threat_cat 12 - Other options */ and est.element_subnational_id not in (select threats.element_subnational_id from el_subnatl_threats_assess threats where threats.d_iucn_threat_category_id = 168))) order by sn.d_name_category_id , sn.scientific_name , natn , subn --, d_iucn_threat_display_order