如何在X上左加入A到B，然后在Y上将B加到C.

Question

I am the world's newest convert to the joys of LEFT JOIN. The following is working beautifully:

$STH = $DBH->prepare("
SELECT cc.cat_subject, cc.cat_major, cc.cat_minor, cc.cat_name, c.cnum, c.unmod
  FROM comment_category cc 
LEFT JOIN comment c
  ON cc.cat_subject = c.cat_subject AND cc.cat_major = c.cat_major AND cc.cat_minor = c.cat_minor
ORDER BY cc.cat_subject, cc.cat_major, cc.cat_minor");

What I want to do now, though, is reference another table favourite (f) which only has a common field cnum with comment (c) and none with comment_category (cc).

I tried the following:

$STH = $DBH->prepare("
SELECT cc.cat_subject, cc.cat_major, cc.cat_minor, cc.cat_name, c.cnum, c.unmod, f.favourite
FROM comment_category cc 
LEFT JOIN comment c 
  ON cc.cat_subject=c.cat_subject AND cc.cat_major=c.cat_major AND cc.cat_minor=c.cat_minor
LEFT JOIN favourite f
  ON c.cnum=f.cnum
ORDER BY cc.cat_subject, cc.cat_major, cc.cat_minor");

It doesn't work. I'm guessing (from what I've read) that this is because I'm actually requesting another join to comment_category (cc) rather than to comment (c).

In short: how do I select from A, LEFT JOIN to B, but then use a field in B to access a value from C?

UPDATE (due to punter displeasure):

The tables are:

comment_category
----------------
subject   (int)
cat_major (int)
cat_minor (int)
name      (varchar)

comment
-------
cnum      (int)
subject   (int)
cat_major (int)
cat_minor (int)
unmod     (varchar)

favourite
---------
cnum      (int)

... I'll stop here - it crashes because there's no favourite field in the favourite table.

My mistake. Sorry, all.

Answer 1

Glad you figured this out on your own. Since you say you are new to JOINs, I did want to add that whenever you are JOINing more than 2 tables together, it's a good idea to consider using an INNER JOIN. The difference, is that a LEFT JOIN will return rows on the LEFT (in this case comment_category) regardless of whether or not you have a match in comment. An INNER JOIN will not return rows from comment_category unless it also has a match in comment.

The reason this is important in this scenario, is because in order for your JOIN on favourite to return anything but NULLs, your query first needs to match on a row from comment_category in comment. If you use an INNER JOIN on comment_category and comment, troubleshooting will be less ambiguous. Also, the use of INNER JOINs will eliminate NULL values from your results (NULLs resulting from the JOIN mismatch, that is).

The use of LEFT vs. INNER JOIN is also dependent on your business requirements. For instance, if all rows in comment_category simply must have a match in comment, then you should use an INNER JOIN. But if you're querying comments that don't necessarily have to have favourites (but you want them returned if there are any), then you should use a LEFT JOIN. Hope this helps.