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 <[EMAIL PROTECTED]>
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
