[thelist] help with SQL and PHP

Paul Cowan evolt at funkwit.com
Tue Dec 10 17:36:00 CST 2002

rudy wrote:
> but seeing as it's mysql, you'll have to choose one of several
> options --

I'm not familiar with mysql, but would something like the following work?

    subcat.catname as subcatname,
    category as cat
    left outer join category as subcat on
        (cat.categoryid = subcat.parentid)
    left outer join menuitem on
        (coalesce(subcat.category, cat.categoryid) = menuitem.categoryid)

Not a fantastic query, but would happily return

catname    subcatname   name
-------    ----------   ----
lunch      <null>       vegemite sandwiches
lunch      monday       <null>
lunch      tuesday      <null>
lunch      wednesday    hot dogs
lunch      wednesday    cabbage soup
breakfast  <null>       corn flakes
breakfast  <null>       dry white toast
breakfast  monday       <null>
breakfast  tuesday      four fried chickens and a coke

In other words: every day, vegemite sandwiches are available for lunch.
Monday and Tuesday have no lunch specials (but a "lunch - monday" row
is returned regardless, which I think you want); Wednesday has hot dogs
or cabbage soup as specials.

Every day for breakfast you can have corn flakes or dry white toast;
no specials on Monday, but on Tuesday you can have four fried chickens
and a coke.

Is this the kind of thing you mean?


More information about the thelist mailing list