Mysql joining 3 tables with possible entries

eecms cats with posts

$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

*/