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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
