[thelist] manipulating rows in the result set with PHP help

[Tom Dell'Aringa]

--- rudy <r937 at interlog.com> wrote:

> i believe the first item in the coalesce list should be
> subcat.categoryID

I changed that first, and got the same results - which is maybe what
you meant anyway, I can't pretent I understand half of this.

> fix your join and then try running it with the following SELECT
> list --
>
> select concat( cat.categoryID
>              , cat.categoryName ) as Category
>      , cat.blurb
>      , cat.parentID
>      , concat( subcat.categoryID
>              , subcat.categoryName ) as SubCategory
>      , menuitem.categoryID
>      , menuitem.item
>      , menuitem.price
>
> this will reveal the keys more easily so you can see what's joined
> with what
>
> also, i think this "coalesce join" will not find menuitems attached
> to a
> category if there are also subcategories attached to the same
> category, so
> you might want to think about how to prohibit that (if that's what
> you'd
> prefer) or how to print them (if you want to allow it)

I replaced the above select and removed what I had, and the result
set came back blank.... waaah!

here is my full statement now:

SELECT concat( cat.categoryID
             , cat.categoryName ) as Category
     , cat.blurb
     , cat.parentID
     , concat( subcat.categoryID
             , subcat.categoryName ) as SubCategory
     , menuitem.categoryID
     , menuitem.item
     , menuitem.price

FROM
    category as cat
    left outer join category as subcat on
        (cat.categoryID = subcat.parentID)
    left outer join menuitem on
        (coalesce(subcat.categoryID, cat.categoryID) =
menuitem.categoryID)

Why do I feel everytime I get close with this it blows up in my face!

Tom

=====
var me = tom.pixelmech.webDeveloper();

http://www.pixelmech.com/
http://www.maccaws.com/
[Making A Commercial Case for Adopting Web Standards]

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com