help with case

6 Apr 2006 3:41 PM

Giscar Paiva

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.br

6 Apr 2006 3:53 PM

Will

it'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

6 Apr 2006 5:12 PM

Giscar Paiva

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

7 Apr 2006 5:34 AM

Omnibuzz

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.

7 Apr 2006 12:17 PM

Giscar Paiva

Omnibuzz wrote:

> 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.

U're right... I just saw that after I post...
Thank u...

6 Apr 2006 3:55 PM

ML

Take a look at this example:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html

Or wait for Joe Celko to guide you to one of his books: "Trees and
Hierarchies".

ML

---
http://milambda.blogspot.com/