
CREATE VIEW [dbo].[SPICE_TaxonSearch_VIEW]
AS
SELECT     dbo.PTaxon.RIdentifier AS TaxonId, dbo.Name.FullNameCache, dbo.Name.NameCache, dbo.Name.Genus, dbo.Name.SpeciesEpi, dbo.Name.InfraSpeciesEpi, dbo.Name.RankFk, dbo.PTaxon.PTRefFk
FROM         dbo.Name INNER JOIN
                      dbo.PTaxon ON dbo.Name.NameId = dbo.PTaxon.PTNameFk
WHERE     (dbo.PTaxon.PTRefFk IN (select refid from SPICE_PublishedSecRefs)) AND dbo.Name.preliminaryflag = 0 AND dbo.Name.RankFk >= 60 AND dbo.f_species2000_getFamily(dbo.PTaxon.RIdentifier) is not NULL
UNION ALL
SELECT     dbo.PTaxon.RIdentifier AS TaxonId, dbo.Name.FullNameCache, dbo.Name.NameCache, dbo.Name.Genus, dbo.Name.SpeciesEpi, dbo.Name.InfraSpeciesEpi, dbo.Name.RankFk, dbo.RelPTaxon.PTRefFk2
FROM         dbo.Name INNER JOIN
                      dbo.PTaxon ON dbo.Name.NameId = dbo.PTaxon.PTNameFk INNER JOIN
                      dbo.RelPTaxon ON dbo.PTaxon.PTNameFk = dbo.RelPTaxon.PTNameFk1 AND dbo.PTaxon.PTRefFk = dbo.RelPTaxon.PTRefFk1
WHERE     (dbo.RelPTaxon.RelQualifierFk = 3) AND (dbo.RelPTaxon.PTRefFk2 IN (select refid from SPICE_PublishedSecRefs)) AND dbo.Name.preliminaryflag = 0 AND 
                      dbo.Name.RankFk >= 60 AND dbo.f_species2000_getFamily(dbo.PTaxon.RIdentifier) is not NULL





GO
