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

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

<p>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 <code>count</code> to be 0 (or NULL) if there are no rows, and the name still returns.</p>

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

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`)
```
```