[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
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
More information about the thelist
mailing list