Outer Join problem 1

[OsakaWebbie]

I thought surely this has been answered many times, but I couldn't find it in a search.

I am re-writing in PHP/MySQL the functionality I had in MS Access, so I'm struggling not to think through queries by using "where some-field in (select some-field from such-and-such). I have a table Person with key PersonID, a table Category with key CategoryID, and a table PerCat with a key of both IDs to form a many-to-many relationship (fairly normal, I think). I want to display checkboxes for all the categories but in two groups - the ones that the person already belongs to, and the ones they don't. Knowing that I couldn't use the "where X in (select X from..." functionality, I tried the following (and several variations), but something isn't working right:

SELECT category.CategoryID, Category, PersonID FROM category LEFT JOIN percat ON Category.CategoryID=percat.CategoryID WHERE PersonID=$pid ORDER BY PersonID, Category

I thought I would get all the categories, with PersonID blank for the ones where there was no PerCat record for that PersonID (ordered by the existence of PersonID, so that I can work with the two cases in order). But it only gives me the records where the PerCat record exists. Does someone see what I am doing wrong? Or do you know a better way to work around MySQL's lack of "where X in (select X..."? Thanks!

 

[swampBoogie]

SELECT category.CategoryID, c.Category, p.PersonID
FROM category c LEFT JOIN percat p
ON C.CategoryID=p.CategoryID
and p.PersonID=$pid
ORDER BY case when p.personid is null then 1 else 0 end,
c.Category

 

[OsakaWebbie]

Thanks! After fixing a couple typos (the category table spelled out in one place and a capital C for the alias in another), your query seems to do what I want. My PHP code doesn't seem to be reading the PersonID values correctly now (it thinks they're all null), but I'll figure that one out. I don't know what "case when p.personid is null then 1 else 0 end" means, but it works!

 

[swampBoogie]

Sorry about the typos. The case is used to sort personid with null at the end of the result. if personid is null the result of the case expression is 1 otherwise 0 and these values will be used when sorting. As the sort order is asc 1 will come after 0 in the result.

A case expresion is similar to iif in Access.

 

[OsakaWebbie]

Oh, I get it now. Having never before even realized you could use a case statement in MySQL, and not knowing the syntax (every language is different) I was reading it as "is null (case #1) then 0 (case #2, "then" indicating order) else 0 (default case) end", which of course doesn't make any sense. But it's "(case when p.personid is null) then 0 else 1", more like an if/then/else construct. I'll have to research that syntax - it might come in handy for other things too. I'm really finding the lack of "where...in (select..." and "where exists (select..." tying my hands sometimes, but I think it's just because I'm not using all that MySQL has to offer.

Thanks a lot for your help.

 

[swampBoogie]

A case statement in SQL is something different than a case expression which is what I used.

case expressions is a ANSI SQL standard feature supported by many DBMSs.

The lack of in/exists is a real drawback as there is a lot of queries that can not be transformed to an equivivalent form in Mysql.