/* find records with more than one primary indicator; */

-- ELEMENT_GLOBAL
SELECT element_global_ref.element_global_id, getsciname(gname_id) GNAME
 , getnamecatdesc(gname_id) name_cat, element_global_ref.reference_id
 , element_global_ref.primary_scientific_name_ind, reference.reference_code 
FROM element_global_ref, reference, element_global
WHERE
 element_global_ref.element_global_id = element_global.element_global_id
 and element_global_ref.reference_id = reference.reference_id 
 and element_global.element_global_id in 
  (select egref.element_global_id 
   from element_global_ref egref 
   where egref.primary_scientific_name_ind = 'Y' 
   having count(egref.reference_id) > 1
   group by egref.element_global_id)
order by element_global.element_global_id


-- ELEMENT_NATIONAL
SELECT element_natl_ref.element_national_id, getsciname(nname_id) NNAME
 , getnamecatdesc(nname_id) name_cat, element_natl_ref.reference_id
 , element_natl_ref.primary_scientific_name_ind, reference.reference_code 
FROM element_natl_ref, reference, element_national
WHERE
 element_natl_ref.element_national_id = element_national.element_national_id
 and element_natl_ref.reference_id = reference.reference_id 
 and element_national.element_national_id in 
  (select enref.element_national_id 
   from element_natl_ref enref 
   where enref.primary_scientific_name_ind = 'Y' 
   having count(enref.reference_id) > 1
   group by enref.element_national_id)
order by element_national.element_national_id


--ELEMENT_SUBNATIONAL
SELECT element_subnatl_ref.element_subnational_id, getsciname(sname_id) SNAME
 , getnamecatdesc(sname_id) name_cat, element_subnatl_ref.reference_id
 , element_subnatl_ref.primary_scientific_name_ind, reference.reference_code 
FROM element_subnatl_ref, reference, element_subnational
WHERE
 element_subnatl_ref.element_subnational_id = element_subnational.element_subnational_id
 and element_subnatl_ref.reference_id = reference.reference_id 
 and element_subnational.element_subnational_id in 
  (select esref.element_subnational_id 
   from element_subnatl_ref esref 
   where esref.primary_scientific_name_ind = 'Y' 
   having count(esref.reference_id) > 1
   group by esref.element_subnational_id)
order by element_subnational.element_subnational_id


--SCIENTIFIC_NAME
SELECT sn.scientific_name_id, sn.scientific_name
 , (select d_name_category.name_category_desc from d_name_category where
    d_name_category.d_name_category_id = sn.d_name_category_id) as NameCat
 , ref.reference_code, sn_ref.primary_name_ref_ind
FROM SCIENTIFIC_NAME sn, scientific_name_ref sn_ref, REFERENCE ref
WHERE sn.scientific_name_id = sn_ref.scientific_name_id
 and sn_ref.reference_id = ref.reference_id
 and sn.scientific_name_id in 
  (select SN_ref.scientific_name_id 
   from scientific_name_ref sn_ref
   where sn_ref.primary_name_ref_ind = 'Y' 
   having count(sn_ref.reference_id) >1
   group by SN_ref.scientific_name_id)
order by sn.scientific_name_id


--MANAGED_AREA
SELECT mao.managed_area_id, mao.owner_name, mao.owner_note,mao.primary_owner_ind
FROM managed_area_owner mao
where mao.managed_area_id in 
(select mao.managed_area_id
from managed_area_owner mao
where mao.primary_owner_ind = 'Y' 
   having count(mao.managed_area_id) > 1
   group by mao.managed_area_id)
order by mao.managed_area_id


--EO
SELECT eo_reference.eo_id, eo_reference.reference_id
 , eo_reference.eo_reference_primary_ind, reference.reference_code 
FROM eo_reference, reference
WHERE
 eo_reference.reference_id = reference.reference_id 
 and eo_reference.eo_id in 
  (select eoref.eo_id 
   from eo_reference eoref 
   where eoref.eo_reference_primary_ind = 'Y' 
   having count(eoref.reference_id) > 1
   group by eoref.eo_id)
order by eo_reference.eo_id


--SOURCE_FEATURE
SELECT source_feature_ref.source_feature_id, source_feature_ref.reference_id
 , source_feature_ref.sf_ref_primary_ind, reference.reference_code 
FROM source_feature_ref, reference
WHERE
 source_feature_ref.reference_id = reference.reference_id 
 and source_feature_ref.source_feature_id in 
  (select sfref.source_feature_id 
   from source_feature_ref sfref 
   where sfref.sf_ref_primary_ind = 'Y' 
   having count(sfref.reference_id) > 1
   group by sfref.source_feature_id)
order by source_feature_ref.source_feature_id