bug #6938
Updated by Andreas Müller almost 6 years ago
Not yet complete * run [s_em_MoveCommonNames] to move common names to structured common names === remove duplicate parent relationships (see also http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication) ~~~ sql SELECT * FROM ( SELECT COUNT(pt2.RIdentifier) AS HowMany, pt.PTNameFk, pt.PTRefFk PTaxon.PTNameFk, PTaxon.PTRefFk , n.FullNameCache Name.FullNameCache FROM PTaxon INNER JOIN RelPTaxon rel ON pt.PTNameFk PTaxon.PTNameFk = rel.PTNameFk1 RelPTaxon.PTNameFk1 AND pt.PTRefFk PTaxon.PTRefFk = rel.PTRefFk1 RelPTaxon.PTRefFk1 INNER JOIN PTaxon pt2 ON rel.PTNameFk2 RelPTaxon.PTNameFk2 = pt2.PTNameFk AND rel.PTRefFk2 RelPTaxon.PTRefFk2 = pt2.PTRefFk INNER JOIN Name n ON pt.PTNameFk PTaxon.PTNameFk = n.NameID Name.NameID WHERE (rel.RelQualifierFk (RelPTaxon.RelQualifierFk = 1) AND (pt.PTRefFk (PTaxon.PTRefFk % 100000 = 0) AND (pt.PTRefFk (PTaxon.PTRefFk >= 7000000) GROUP BY pt.RIdentifier, pt.PTNameFk, pt.PTRefFk, n.FullNameCache) PTaxon.RIdentifier, PTaxon.PTNameFk, PTaxon.PTRefFk, Name.FullNameCache) AS DuplicateParentRelation WHERE HowMany > 1 ORDER BY HowMany DESC; ~~~ ~~~ sql SELECT n2.fullNameCache, rel.* FROM PTaxon pt INNER JOIN RelPTaxon rel ON pt.PTNameFk = rel.PTNameFk1 AND pt.PTRefFk = rel.PTRefFk1 INNER JOIN Name n2 ON rel.PTNameFk2 = n2.NameId WHERE rel.PTNameFk1 = xxx AND rel.PTRefFk1 = 7xxx ~~~