[thelist] help with SQL and PHP

[Paul Cowan]

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

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

SELECT
    cat.catname,
    subcat.catname as subcatname,
    menuitem.name
FROM
    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?

Paul