help with case

Good afternoon...
I look for a forum which one I can as for queries.
If this isn't the one, sorry. Please say me where I can find it.
My problem:
I have a table where ParentID is foreign key to ID in the same table.
the others columns are type, value and author.
Type can get 3 values: FAMILY, GENUS and SPECIES.
To take all the FAMILY-GENUS-SPECIES from the table I use:
select distinct
case 'FAMILY'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as FAMILY,
case 'GENRE'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as GENRE,
case 'SPECIES'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as SPECIES
from TaxonName TN
left outer join TaxonName TN1 on TN1.TaxonNameID = TN.ParentTaxonNameID
left outer join TaxonName TN2 on TN2.TaxonNameID = TN.ParentTaxonNameID
and it worked fine. But I want to take the major level author not NULL
too. For example:
if SPECIES is NOT NULL return author from register where type is SPECIES;
else if GENRE is NOT NULL return author from register where type is GENRE;
else if FAMILY is NOT NULL return author from register where type is FAMILY;
Anyone can help me?
thanks for the help
Giscar Paiva
www.cria.org.brit's not entirely clear what you want, but perhaps this will help:
SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
CASE etc...
Cheers
Will|||Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Or wait for Joe Celko to guide you to one of his books: "Trees and
Hierarchies".
ML
http://milambda.blogspot.com/|||Will wrote:
> it's not entirely clear what you want, but perhaps this will help:
I'll try to iluminate you more...
in the table, I can have the author in all records. For Example...
if my table is:
ID ParentID Type Value Author
---
1 <null> FAMILY Leguminosae Britton
2 1 GENUS Leucaena Rose
3 2 SPECIES diversifolia Zarate
---
I want the query returns:
FAMILY GENUS SPECIES AUTHOR
----
Leguminosae <null> <null> Britton
Leguminosae Leucaena <null> Rose
Leguminosae Leucaena diversifolia Zarate

> SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
> CASE etc...
The problem is that I don't know if the tn is Family, Genus OR Species,
'cause my CASE.
But thanks anyway...
Giscar Paiva
www.cria.org.br|||add this as another column
coalesce(tn.author, tn1.author,tn2.author)
Btw, just observed that your join condition is wrong. TN1 and TN2 are joined
the same way to TN. I am surprised you are saying its working fine.|||Omnibuzz wrote:
> Btw, just observed that your join condition is wrong. TN1 and TN2 are join
ed
> the same way to TN. I am surprised you are saying its working fine.
U're right... I just saw that after I post...
Thank u...sql