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 replacedSynonym, n1.Created_Who, n1.nameId, n2.NameCache n2, n2.FullNameCache n2f, n2.created_Who, n2.nameId, rel.RelNameId, rel.RefFk relRef, rel.RelNameQualifierFk, ta.*
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 replacedSynonym, n1.Created_Who, n1.nameId, n2.NameCache nomNov, n2.FullNameCache nomNovAuthor, 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
~~~
Der eine "is alternative name for" Fall wird in #7317 abgehandelt.