That query should work, your thoughts on LEFT JOIN is correct, what Database are you using.
A left join should get all categories, and all items for each category. If you have a category with no items, the item columns will return as null. Unless there is a where statement your not showing, and it's hard to see which tables the select fields come from as you haven't prefixed the table to the columns. Regards Dale Fraser <http://dalefraser.blogspot.com> http://dalefraser.blogspot.com From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Seona Bellamy Sent: Tuesday, 4 September 2007 4:54 PM To: CFAussie Subject: [cfaussie] [SOT] Left join not behaving as expected Hi guys, I'm playing around with recursion this afternoon, and I've hit a snag which I think is due to my less-than-perfect SQL skills. I have the following tables: CATEGORY - cat_id - cat_name - parent_id ITEM - id - title - category_id I've managed to get it working where I pull out a list off all the categories, sort them by parent_id, and then run a recursive function over the query to transform them into a nested list. Now I want to add another level of complexity, where if a category has items attached to it, those items will nest in under the relevant list item. So you might end up with something like: Category 1 - Subcategory 1 - Subcategory 2 -- -- Sub-Subcategory 1 --- --- --- Item 1 --- --- --- Item 2 -- -- Sub-Subcategory 2 --- --- --- Item 3 -- -- Item 4 -- -- Item 5 - Item 6 So I have the following query: SELECT cat_id, cat_name, parent_id, title, id FROM category c LEFT JOIN item i ON c.cat_id = i.category_id ORDER BY parent_id, cat_name, title >From the way I understand LEFT JOIN, this should give me all of the categories, whether they have an item directly attached to them or not. That's not what's happening, however. Judging by the dump of the query results, I only seem to be returning information for the items, including their category information. Have I misunderstood the way it works? Or have I just constructed it wrongly? Any advice would be greatly appreciated. Cheers, Seona. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
