[thelist] manipulating rows in the result set with PHP help
Tom Dell'Aringa
pixelmech at yahoo.com
Wed Dec 11 11:17:01 CST 2002
--- 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
More information about the thelist
mailing list