Re: Funky SELECT with Oracle8

SELECT c1.*, c2.name
FROM CATEGORIES c1, CATEGORIES c2
WHERE (c1.parent_cat_id = c2.cat_id)

In article <8eaecl$ab$1_at_nnrp1.deja.com>,   jeddings_at_jeddings.com wrote:
> 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.
>

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