Re: Funky SELECT with Oracle8

Just join the table to itself

from categories c1, categories c2

To know this you don't have to be
a sql nerd
b bold
just need to read and learn.

Hth,

Sybrand Bakker, Oracle DBA

<jeddings_at_jeddings.com> schreef in berichtnieuws 8eaecl$ab$1_at_nnrp1.deja.com...
> 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 Fri Apr 28 2000 - 00:00:00 CDT