Doug Brown wrote: > See, there I go again not making my self clear. The table actually has more > than just makes and models of cars. > It has antiques, autos, furniture, blah blah. each parent category has it's > child category and each child has it's own child. As an example, Antiques > would have a child of asian antiques and it would have a child of all the > different asian antiques you could think of. I hope this makes more sense. > Anyhow the first group of childs I can refer back to their parent, but the > second group of childs are referenced back to the child of the parent and > not the parent directly. > > Well, if it is just three levels, then you could possibly just add in another join... SELECT a.category AS level1, b.category AS level2, c.category AS level3 FROM categories a LEFT JOIN categories b ON a.cat_id = b.parent_id AND a.parent_id IS NULL LEFT JOIN categories c ON b.cat_id = c.parent_id AND b.parent_id IS NOT NULL ORDER BY a.category,b.category,c.category
If you are dealing with a indeterminate number of levels and still want it returned in a single dataset, then you probably want to go to a SP...I'd google on something like "SQL parent child recursion" to get some ideas. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254649 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
