Thanks Dale. I guess it just made me stop and think and try to figure out if there was a better way than what I'd been doing.
Cheers, Seona. On 05/09/07, Dale Fraser <[EMAIL PROTECTED]> wrote: > > I do the same, ie only prefix when needed. > > The only advantage is readability, I don't think there is any speed > adavantage. > > I was just asking to try to fix the query. > > > Regards > > Dale Fraser > > > > http://dalefraser.blogspot.com > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Seona Bellamy > *Sent:* Wednesday, 5 September 2007 8:41 AM > *To:* [email protected] > *Subject:* [cfaussie] Re: [SOT] Left join not behaving as expected > > > > Yep, you sure did. :) Serves me right for typing it instead of cutting and > pasting! > > Just out of curiosity, you mentioned that I hadn't prefixed the table > columns, but is there any real advantage to doing that when there's no > ambiguity in the column names? I tend to only do it when I need to, but I'm > interested to know if I'm missing out on some benefit here. > > Cheers, > > Seona. > > On 04/09/07, *Dale Fraser* <[EMAIL PROTECTED]> wrote: > > I guessed correctly then :P > > > > > > Regards > > Dale Fraser > > > > http://dalefraser.blogspot.com > > > > *From:* [email protected] [mailto:[EMAIL PROTECTED] *On > Behalf Of *Dale Fraser > *Sent:* Tuesday, 4 September 2007 5:23 PM > *To:* [email protected] > *Subject:* [cfaussie] Re: [SOT] Left join not behaving as expected > > > > 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 > > > > *From:* [email protected] [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 [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 -~----------~----~----~----~------~----~------~--~---
