Created
February 23, 2012 17:49
-
-
Save GDmac/1894005 to your computer and use it in GitHub Desktop.
Mysql joining 3 tables with possible entries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$query = " | |
SELECT | |
c.cat_id, cat_name, count(t.status) AS count_posts | |
FROM | |
exp_categories c | |
LEFT JOIN exp_category_posts AS p | |
ON p.cat_id = c.cat_id | |
LEFT JOIN exp_channel_titles AS t | |
ON t.entry_id = p.entry_id | |
AND t.status != 'closed' | |
AND t.entry_date > UNIX_TIMESTAMP(NOW()) | |
AND t.expiration_date > UNIX_TIMESTAMP(NOW()) | |
AND t.channel_id = '1' | |
WHERE | |
c.group_id = 5 | |
GROUP BY | |
c.cat_id | |
ORDER BY | |
c.group_id, c.cat_order; | |
" | |
/* | |
If the conditions would have been in the WHERE clause, then an entire row, | |
not matching 'all' conditions would have been discarded from the result. | |
e.g. the whole row for a found category, but having zero posts, would have been tossed out. | |
However, if the conditions are in the JOIN clause, then any records for that table, | |
with those matching conditions, will be joined into the result. In this example those | |
rows are counted, so all resulting rows for t.titles are collapsed into a count() per category. | |
via: http://stackoverflow.com/questions/3469643/mysql-multiple-left-outer-join-query-question-involving-3-tables | |
Now we have all categories, and the count of the posts, in one swoop | |
cat_id count_posts | |
40 1 | |
46 0 | |
49 0 | |
42 1 | |
47 1 | |
50 0 | |
41 1 | |
45 0 | |
51 0 | |
44 0 | |
48 0 | |
*/ | |
/* | |
For completeness, here's the example with | |
the conditions in the WHERE clause | |
*/ | |
$query = " | |
SELECT | |
c.cat_id, cat_name, count(t.status) AS count_posts | |
FROM | |
exp_categories c | |
LEFT JOIN exp_category_posts AS p | |
ON p.cat_id = c.cat_id | |
LEFT JOIN exp_channel_titles AS t | |
ON t.entry_id = p.entry_id | |
WHERE | |
c.group_id = 5 | |
AND t.status != 'closed' | |
AND t.entry_date > UNIX_TIMESTAMP(NOW()) | |
AND t.expiration_date > UNIX_TIMESTAMP(NOW()) | |
AND t.channel_id = '1' | |
GROUP BY | |
c.cat_id | |
ORDER BY | |
c.group_id, c.cat_order; | |
" | |
/* | |
cat_id count_posts | |
40 1 | |
42 1 | |
47 1 | |
41 1 | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment