[thelist] help with SQL and PHP

[rudy]

> So Question #1 - how can I pull all the categories in my SQL above
> as well as what I am already getting.

this is a trickier question than you realize

you're currently getting each menuitem with its category via the first,
inner join

however, if a menuitem can exist without belonging to a category, then, as
you discovered, it won't be included in the inner join

category-to-menuitem is one-to-many, or, if you turn it around,
menuitem-to-category is one-to-one, but that's only part of the story

can menuitem-to-category be zero-to-one? or must each menuitem have a
category?

if you allow a menuitem to exist without belonging to a category, then you
have a foreign key that may be null, a perfectly valid arrangement,
depending on the business rules that the model is supposed to support

it's not a question of which is easier for you, inner joins or outer joins,
but rather which database relationship more accurately models this
particular restaurant

if this restaurant can have no-category menuitems, you need to use an outer
join in those circumstances where you want the query to return all menuitems

okay, deep breath

as for returning categories with no menuitems, consider for the moment that
there are no subcategories, just categories and menuitems

the other side of the category-menuitem relationship is whether a category
can exist without any menuitems in it

assuming the answer is yes, then you'd need an outer join in that direction,
and if you combine that with the above requirement, menuitems without
categories, then you are looking at doing a "full outer join"

complicating the whole deal is the category-subcategory structure, with the
likelihood that menuitems can be related to either a subcategory or a
category, and that a category may or may not have any subcategories (another
outer join in the offing) and you end up with very complicated retrieval
requirements indeed, assuming you wanted to print everything out, nicely
nested, with menuitems at any level, or not at all, and categories with or
without menuitems, and with or without subcategories...

so when you ask how to fix your sql, i'm not really sure how to answer that
because i don't really know whether you plan to allow these optional
relationships to continue


rudy