Postgres SQL gathering data from two tables using an id from a 3rd table

Question

I have 3 tables;

• idmaster
• data1
• data2

idmaster has every id. I need to count how many times each id exists in both data1 and data2 and output them seperate.

id comes from idmaster. Ideally would like to have all of them, even if no data exists, but thats not as important. data1 and data2 do have id columns.

Example

id	data1 cnt	data2 cnt
A	104	20
B	12	4
C	0	0

I tried this, but it gives me some nonsense numbers, data1 and data2 had the exact same number which doesnt match up to my data.

SELECT idmaster.id, count(data1.*), count(data2.*) FROM idmaster, data1, data2
WHERE idmaster.id = public.data1.id 
AND idmaster.id = public.data2.id
GROUP BY idmaster.id

Results:

id	data1 cnt	data2 cnt
A	160	160
B	66	66
C	7	7

I'm expecting something like this:

Example

id	data1 cnt	data2 cnt
A	104	20
B	12	4
C	0	0

Answer 1

You could use left join to find the counts of ids separately in data1, data2 tables, then join this two separate queries to get the desired output.

with data1_counts as
(
  select M.id, count(D1.id) data1_cnt from 
  idmaster M left join data1 D1
  on M.id=D1.id
  group by M.id
),
data2_counts as 
(
  select M.id, count(D2.id) data2_cnt from 
  idmaster M left join data2 D2
  on M.id=D2.id
  group by M.id
)

select dt1.id, dt1.data1_cnt, dt2.data2_cnt
from data1_counts dt1 join data2_counts dt2
on dt1.id=dt2.id
order by dt1.id

See a demo.

Answer 2

You can use two "scalar subqueries" (line #2 and #3 below) to compute the count of related rows. For example:

select id,
  (select count(*) from data1 d where d.id = m.id) as data1_cnt,
  (select count(*) from data2 d where d.id = m.id) as data2_cnt
from idmaster m