Returning 0 with COUNT()

[lemmin]

If I have these three tables:

 

Table 1

t1ID  [other fields]

 

Table 2

t2ID  t1ID  [other fields]

 

Table 3

t3ID  t1ID  [other fields]

 

If I am only paying attention to, let's say, t1ID = 2 (from Table 1), is there a way to get a count of how many entries in Table 2 and Table 3 have a t1ID = 2 and still return 0 in that case? I want to make sure I still get results in the case that one or both have 0 entries that match. The only way I have been able to do this is with subqueries, but it seems really inefficient to do so (especially when I have to do it for multiple tables).

 

Thanks for any help.

[Mchl]

Don't know how you tried it, but this should work and be pretty fast

SELECT
  t1.ID,
  t2.cnt,
  t3.cnt
FROM
  table1 AS t1
LEFT JOIN
  (SELECT t1ID, COUNT(*) AS cnt FROM table2 GROUP BY t1ID) AS t2
USING (t1ID)
LEFT JOIN
  (SELECT t1ID, COUNT(*) AS cnt FROM table3 GROUP BY t1ID) AS t3
USING (t1ID)
WHERE t1ID = 2