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

rudy r937 at interlog.com
Wed Dec 11 10:52:01 CST 2002

> http://www.pixelmech.com/test/results.html

nice job

i really like a guy who documents as he goes

coalesce picks the first non-null value going from left to right

you have

    coalesce(subcat.categoryName, cat.categoryID)
           = menuitem.categoryID

if there is a subcat for the cat, then subcat.categoryName will not be null

but then you are matching a category name to the menuitem's categoryID

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

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)


More information about the thelist mailing list