Funky SELECT with Oracle8

From: <jeddings_at_jeddings.com>
Date: 2000/04/27
Message-ID: <8eaecl$ab$1@nnrp1.deja.com>#1/1

Hrm...this is what I'm trying to do. I hope some SQL nerd out there can help me out with this one... ;-)

I have a CATEGORIES table:

cat_id         integer       primary key
name           varchar(300)

parent_cat_id integer

Let's say I have three rows in this table:

CAT_ID NAME PARENT_CAT_ID

1       Animals      NULL
2       Dogs         1
3       Cats         1

(The top-level "Animals" category contains two others: "Dogs" and "Cats".)

What I'm trying to do is to SELECT out the rows that have some search criteria (say they contain the letter "a"). But what I'd like to do is to SELECT back not only the name of the category, but also the name of the parent category. In this case, I want it to SELECT back:

Animals, NULL
Animals, Cats

But alas! I have no way of referring to the parent_cat_id! If I do a WHERE parent_cat_id = cat_id, it, of course, tries to compare the two id's of the CURRENT row. This is a simple join if the parent category information is in another table; it has me stumped being in the same one.

Anyone bold enough to give this one a shot? ;-)

--Jeff

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 27 2000 - 00:00:00 CDT