How many B and C does A have?

Question

I have the following tables:

A: id 1 2 B: id a_id 1 1 2 1 3 1 C: id a_id 1 1 2 1 3 2 

I need this result:

 A, CountB, CountC 1, 3, 2 2, 0, 1 

This attempt does not work fine:

 SELECT A.id, COUNT(B.id), COUNT(C.id) FROM A LEFT JOIN B ON A.id = B.a_id LEFT JOIN C ON A.id = C.a_id GROUP BY A.id 

How should a sql clause be without using correlation queries?

Answer 1

The following change should work on yours:

 SELECT A.id, COUNT(distinct B.id), COUNT(distinct C.id) FROM A LEFT JOIN B ON A.id = B.a_id LEFT JOIN C ON A.id = C.a_id GROUP BY A.id 

However, there are those (like me) who believe that using count count is copying. The problem is that the lines from B and from C interfere with each other, multiplying by the union. Thus, you can also make each connection independently, and then combine the results:

 select ab.id, cntB, cntC from (select a.id, count(*) as cntB from A left outer join B on A.id = B.a_id group by a.id ) ab join (select a.id, count(*) as cntC from A left outer join C on A.id = C.a_id group by a.id ) ac on ab.id = ac.id 

For simple counting, the first form is fine. If you need to do other generalizations (e.g. summing up a value), then you usually need to split into component requests.