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