Project

General

Profile

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.

Back