[thelist] help with SQL and PHP

[Paul Cowan]

Tom Dell'Aringa wrote:
> Now - can someone explain to me just what coalesce does?

Ahh, the easy part. I like this part.

I'll explain for SQL Server here, and assume no cross-DB differences.

COALESCE is a fancy version of ISNULL which accepts more than two
parameters.

If you know what ISNULL does, you can stop reading now; otherwise,
I'll tell ya.

COALESCE/ISNULL are functions which return the first non-<null> argument
passed to them.

For example, ISNULL(null, 5) returns 5; ISNULL(6, 7) returns 6.
COALESCE(null, null, 8, 9, null) returns 8.

In the context of the query I gave you, the
    COALESCE(subcat.categoryid, cat.categoryid)
in the join effectively says 'join on the subcategoryID, if there is
a subcategoryID there' -- that is, if the
    left outer join category as subcat ...
found any subcategories matching the category.

If there were no subcategories for the category, the subcat.categoryid
would be null, so it would instead find any menuitems which had a
categoryID of the "parent" category.

Cheers,

Paul.