SQL: select count from 3 tables with join ?

Databases
1

I have 3 tables :
table A : idA (integer) - nameA (varchar)
table B : idB (integer) - idA (integer linked to A) - nameB (varchar)
table C : idC (integer) - idB (integer linked to B) - nameC (varchar)

I want to select all tableA records, counting the linked B and C for each A

I am able to get 1 level (A and B) but can’t get the C count column :

select nameA, (select count(*) from tableB as B where B.idA=A.idA) from tableA as A

any idea ?
thanks.

next exercice : do the same for one more table D linked to C …

2

I think this would work…

select
  count( distinct a.id ) as a_count,
  count( distinct b.id ) as b_count,
  count( c.id ) as c_count
from
  a
  left join b on (b.idA = a.id)
  left join c on (c.idB = b.id)
3

No, that won’t do it, that will give you the aggregates. How about this from PostgreSQL:

select
  count( a.id ) over() as a_count,
  a.id as a_id,
  count( a.id ) as b_count,
  count( b.id ) as c_count
from
  a
  left join b on (b.idA = a.id)
  left join c on (c.idB = b.id)
group by
  a.id,
  b.id
4 
select *, count(distinct b.id ), count(distinct c.id) from tableA as a Left Join tableB as b ON (a.id = b.idA) left join tableC as c on(b.id = c.idB) group by a.id
5

well this seems to work, finally :

select nameA, 
           (select count(*) from tableB as B where B.idA=A.idA),
           (select count(*) from tableC join tableB as B using (idB) where B.idA=A.idA)
           (select count(*) from tableD join tableC as C using (idC) join table B as B using (idB) where B.idA=A.idA)
from tableA as A