How many B and C has A?

Question

I have this 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 try doesnt 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 must be the sql sentence without using correlative queries?

Answer 1

The following variation on yours should work:

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 (such as myself) who feel that using count distinct is a cop-out. The problem is that the rows from B and from C are interfering with each other, multiplying in the join. So, you can also do each join independently, and then put the results together:

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 just counting, the first form is fine. If you need to do other summarizations (say, summing a value), then you generally need to split into the component queries.