MySQL Cross-Table Count(*) Query Help

Question

SELECT `name` , COUNT(*) AS `count`
FROM `t1`, `t2`
WHERE `t2`.`id` = `t1`.`id`
GROUP BY `t2`.`id`

I want to obtain the name from t1 and the number of rows in t2 where the id is the same as on t1.

I've got the above so far, however it won't return any data if there are no rows in t2 that match. I'd prefer count to be 0 (or NULL) if there are no rows, and the name still returns.

Edit: I'd like to be able to sort by the count descending. (or name ASC) is that possible?

Answer 1

Do a union to get the other set SELECT name , COUNT(*) AS count FROM t1, t2 WHERE t2.id = t1.id GROUP BY t2.id UNION Select name, 0 as count from t1 where NOT EXISTS (select 1 from t2 where t2.id = t1.id)

Answer 2

This should work for you:

SELECT `t1`.`id` , COUNT(`t2`.`id`) AS `count`
FROM `t1` LEFT JOIN `t2` ON `t1`.`id` = `t2`.`id`
GROUP BY `t1`.`id`

Left join ensures you have all rows from t1, and COUNT(t2.id) makes it count only records where t2.id is not null (that is - those that really exist in t2)

Answer 3

This sorts descending by COUNT, and within same counts ascending by name. Names with no rows in t2 will return with a count of 0.

SELECT 
  `t1`.`name`, 
  COUNT(`t2`.`id`) AS `count`
FROM 
  `t1` 
  LEFT JOIN `t2` ON`t2`.`id` = `t1`.`id`
GROUP BY
  `t1`.`name`
ORDER BY
  COUNT(`t2`.`id`) DESC,
  `t1`.`name`

Modify the ORDER BY to your needs.