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, n1.Created_Who laterHomonymCreated, n1.nameId laterHomonymId, n2.NameCache earlierName, n2.FullNameCache earlierNameAuthor, n2.created_Who earlierNameCreated, n2.nameId earlierNameId, 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
~~~
Homonym candidates:
~~~ sql
SELECT NameId, rd.FullNomRefCache, FullNameCache, pt.PTRefFk, rd.FullRefCache, at.AuthorTeamCache, at.FullAuthorTeamCache, RankFk, NameCache, UnnamedNamePhrase, n.PreliminaryFlag, SupragenericName, Genus, GenusSubdivisionEpi, SpeciesEpi, InfraSpeciesEpi, AuthorTeamFk, ExAuthorTeamFk,
BasAuthorTeamFk, ExBasAuthorTeamFk, HybridFormulaFlag, MonomHybFlag, BinomHybFlag, TrinomHybFlag, CultivarGroupName, CultivarName, NomRefFk, NomRefDetailFk, NameSourceRefFk, Source_Acc,
n.Created_When, n.Created_Who, n.Notes, ParsingComments, OldNomRefFk, OldNomRefDetailFk, n.Updated_Who, OrthoProjection, HybridFormula, TempKewId, n.uuid
FROM Name n
LEFT OUTER JOIN RefDetail rd ON rd.RefDetailId = n.NomRefDetailFk
LEFT OUTER JOIN AuthorTeam at ON at.AuthorTeamId = n.AuthorTeamFk
LEFT OUTER JOIN PTaxon pt ON pt.PTNameFk = n.NameId
WHERE (NameId IN (-1)) OR
(FullNameCache LIKE 'Taraxacum laciniatum%')
ORDER BY FullNameCache, PTRefFk, NameID
~~~
Alternative name:
Der eine "is alternative name for" Fall wird in #7317 abgehandelt.