/* Threat_grid Global 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 egt.element_global_id , egt.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 GNAME -- , d_threat_cat.DISPLAY_VALUE IUCN_THREAT_CATEGORY -- , d_threat_cat.d_iucn_threat_category_id 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_global egt -- , el_global_threats_assess threats -- , d_iucn_threat_category d_threat_cat WHERE egt.gname_id = sn.scientific_name_id -- and egt.ELEMENT_GLOBAL_ID = threats.element_global_id -- and threats.d_iucn_threat_category_id = d_threat_cat.D_IUCN_THREAT_CATEGORY_ID and egt.inactive_ind = 'N' and ( (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (2,3,4) )/* threat_cat_desc - 1 - Residential and commercial*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 1)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 5)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 6)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 7)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 8)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (102, 103, 104)) /* threat_cat 2.4 Marine and freshwater aquaculture*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 9)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (11,12,13)) /* threat_cat 3-Energy Production and Mining*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 10)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (15,16,17,18)) /* threat_cat 4-Transportation and Service Corridors */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 14)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 19)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 20)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (109, 110, 111, 112)) /* threat_cat 5.2 - Gathering terrestrial plants */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 21)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 22)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 23)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (25,26,27)) /* threat_cat 6-Human instrusions and disturbance */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 24)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 28)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (124, 125, 126)) /* threat_cat 7.1 - Fire and fire suppression*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 29)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 30)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 66)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 32)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (138, 139)) /* threat_cat 8.1 - Invasive non-native/alien species/diseases*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 33)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (140, 141)) /* threat_cat 8.2 - Problematic native species/diseases*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 34)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (143, 144)) /* threat_cat 8.4 - Problematic species/diseases of unknown origin*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 142)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (146, 147)) /* threat_cat 8.5 - Viral/prion-induced diseases*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 145)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 36)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (149, 150, 151)) /* threat_cat 9.1 - Domestic and urban waste water */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 37)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (152, 153, 154)) /* threat_cat 9.2 - Industrial and military effluents */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 38)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 39)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 41)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (163, 164, 165, 166)) /* threat_cat 9.6 - Excess energy*/ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 42)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (44,45,46)) /* threat_cat 10-Geologic Events */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 43)) OR (egt.element_global_id in (select threats.element_global_id from el_global_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 egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 47)) OR (egt.element_global_id in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id in (169)) /* threat_cat 12 - Other options */ and egt.element_global_id not in (select threats.element_global_id from el_global_threats_assess threats where threats.d_iucn_threat_category_id = 168))) order by sn.d_name_category_id , sn.scientific_name -- , d_iucn_threat_display_order