task #7316
Updated by Andreas Müller about 6 years ago
Currently in v_cdm_exp_namesRelatedTo we export all name relations of type 1 is basionym for => questionable as all new combinations should be also synonyms (but maybe similar names were mixed up sometimes?) 2 is later homonym of !! => anyway check if there is not maybe a duplicate 3 is replaced synonym for => questionable as all nom. nov. should be also synonyms (but maybe similar names were mixed up sometimes?) 9 is first parent of => questionable if hybrids of accepted taxa should be exported too if not being an explicit taxon 10 is second parent of => see 9) 16 is orthographic variant of => this should not happen as the taxa/synonyms should not be variants The following relations we do not export because: looks like they all do not have data that do not yet exist as taxa 4 is validation of 5 is later validation of 6 is type of 7 is conserved type of 8 is rejected type of 11 is female parent of 12 is male parent of 13 is conserved against 14 is rejected in favour of 15 is treated as later homonym of 17 is alternative name for 18 has same type as 37 is later isonym of 61 is lectotype of 62 type not designated ~~~ sql SELECT n1.FullNameCache fromName, n1.Created_Who, n1.nameId, n2.NameCache toName, n2.FullNameCache toNameAuthor, n2.created_Who, n2.nameId, rel.RelNameId, rel.RefFk relRef, rel.RelNameQualifierFk, ta.RIdentifier, ta.PTRefFk FROM RelName rel INNER JOIN v_cdm_exp_taxaAll ta ON ta.PTNameFk = rel.NameFk1 INNER JOIN Name n1 ON n1.nameId = rel.NameFk1 INNER JOIN Name n2 ON n2.nameId = rel.NameFk2 WHERE n2.nameId NOT IN (SELECT ta2.PTNameFk FROM v_cdm_exp_taxaAll ta2) -- AND n2.NameCache IN (SELECT nn.NameCache FROM v_cdm_exp_taxaAll ta2 INNER JOIN Name nn ON ta2.PTNameFk = nn.NameId ) ORDER BY RelNameQualifierFk, ta.PTRefFk, n1.FullNameCache ~~~ v_cdm_exp_namesRelatedFrom: ~~~ sql SELECT n1.FullNameCache fromName, n1.Created_Who, n1.nameId, n2.NameCache toName, n2.FullNameCache nomNovtoNameAuthor, n2.created_Who, n2.nameId, rel.RelNameId, rel.RefFk relRef, rel.RelNameQualifierFk, q.RelNameQualifier, ta.RIdentifier, ta.PTRefFk FROM RelName rel INNER JOIN v_cdm_exp_taxaAll ta ON ta.PTNameFk = rel.NameFk2 INNER JOIN Name n1 ON n1.nameId = rel.NameFk1 INNER JOIN Name n2 ON n2.nameId = rel.NameFk2 INNER JOIN RelNameQualifier q ON q.RelNameQualifierId = rel.RelNameQualifierFk WHERE n1.nameId NOT IN (SELECT ta2.PTNameFk FROM v_cdm_exp_taxaAll ta2) ta2.PTNameFk = nn.NameId ) -- AND RelNameQualifierFk NOT IN (2, 16) -- AND RelNameQualifierFk IN (3) ORDER BY RelNameQualifierFk, ta.PTRefFk, n1.FullNameCache ~~~ Potential homonym duplicates: ~~~ sql SELECT n1.FullNameCache laterHomonym, fromName, n1.Created_Who laterHomonymCreated, fromNameCreated, n1.nameId laterHomonymId, fromNameId, n2.NameCache earlierName, toName, n2.FullNameCache earlierNameAuthor, toNameAuthor, n2.created_Who earlierNameCreated, toNameCreated, n2.nameId earlierNameId, toNameId, rel.RelNameId, rel.RefFk relRef, rel.RelNameQualifierFk, q.RelNameQualifier, ta.RIdentifier, ta.PTRefFk FROM RelName rel INNER JOIN v_cdm_exp_taxaAll ta ON ta.PTNameFk = rel.NameFk2 INNER JOIN Name n1 ON n1.nameId = rel.NameFk1 INNER JOIN Name n2 ON n2.nameId = rel.NameFk2 INNER JOIN RelNameQualifier q ON q.RelNameQualifierId = rel.RelNameQualifierFk WHERE n1.nameId NOT IN (SELECT ta2.PTNameFk FROM v_cdm_exp_taxaAll ta2) AND EXISTS (SELECT nn.NameCache FROM v_cdm_exp_taxaAll ta2 INNER JOIN Name nn ON ta2.PTNameFk = nn.NameId WHERE nn.nameId <> n1.nameId AND nn.nameId <> n2.nameId AND nn.NameCache = n2.NameCache AND nn.nameCache <> nn.FullNameCache) AND RelNameQualifierFk IN (2) -- AND (n1.FullNameCache > 'Geranium laevigatum DC.' OR PTRefFk > 7400000) AND PTRefFk > 7300000 ORDER BY RelNameQualifierFk, ta.PTRefFk, n1.FullNameCache ~~~ Der eine "is alternative name for" Fall wird in #7317 abgehandelt.