Self Join Question

[Bertramp]

Hi People,

I have a table containing category and another one containing products - each product is has a reference to a category - each category has a field defining a parent id - now this is what i want to do

I would like to have each of the products listed with a field displaying the hierarchy the product belongs to - e.g systemsesktop Computersell

Thanks

Bert

 

[PHV]

For a self join you need 2 instances of the table with different aliases.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bertramp]

Yes - i do understand that - but my confusion is - how many instances do i add to get all node information in a field for a current record - e.g. the current record shows a product called computer the field describing it should be

business:IT:Systemsesktop | Computer

the first field would describe that the product computer belongs to the desktop category which is a child of the Systems category which in turn is a child of IT and IT is a child node of business

Regards

Bert

 

[PHV]

Use left outer joins and as many instances of the category table than the deepest level.
I don't know an easy way to do recursive SQL ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

here's an article which goes into a bit more detail on PHV's suggestion to use as many outer joins as necessary --

Categories and Subcategories

r937.com | rudy.ca