Re: Recursive SQL query? - Ah hah!

Someone in the local LUG group put me onto a possible way to accomplish it - not sure if what I took from their message was what they were getting at but it would seem to at least begin to address a possible way to do the inheritance!

included response:
---------------------------
Now there's an interesting way to look at it. Not sure if this iswhat you mean, but at the time an item was inserted, just addadditional links for each of the parents with another column and ainteger designator as to how many 'steps' below that item is. Sosearching for the immediate parent would be a 1, another link to that parents parentwould be a 2 and so forth. Say a column called descendency orrelationship (or rel for short). Then looking for immediate parent linkswould just require a WHERE rel=1, and inherited relationships wouldjust use a ORDER BY rel ASC.
That might also be useful for statistics and management. i.e. what's the deepest tree? hmm ok, MAX rel, how many descendants fall under x? SELECT count(*) FROM categoryLinks WHERE ancestorID = x AND rel = 1 (assuming i change parentID to ancestorID of course)

Now once again, my understanding of SQL falls short - I know there aresome means for finding unique values returned, would there be any wayfor restricting to the 'first' to have a non-null? I'm sure you couldprobably do it looking up one field at a time, how about the structurefor pulling multiple fields returning the first with a value for each?

SW

----- Original Message ----
From: Tom
To: Scott Webster Wood
Sent: Tuesday, April 15, 2008 5:17:09 PM
Subject: Re: [WLUG] OT: recursive SQL?

I was thinking you would do this with one table with N joins,
depending on the depth you wanted to go.

ID | p_ID | name | property

And outer join on ID to p_ID.
Top parent would have p_ID = 0





____________________________________________________________ ________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ