I have 4 tables, MASTER, TRANS1, TRANS2 and TRANS3. I want to get count by USERID in table MASTER from tables TRANS1-3.
This show tables for references.
Master table:
USERID REF
--------------------
1 Alfa
2 Beta
3 Charlie
4 Delta
5 Echo
TRANS1 table
Id USERID
------------
1 1
2 1
3 2
4 3
5 5
TRANS2 table
Id USERID
------------
1 2
2 3
3 4
4 5
I want to return to another table or view like this
USERID COUNT_FROM_TRANS1 COUNT_FROM_TRANS2 COUNT_FROM_TRANS3
--------------------------------------------------------------------
1 2 0 1
2 1 1 2
3 1 1 3
4 0 1 4
5 1 5 5
How does this work with SQL Server 2014?
In this case, correlated subqueries might be the simplest solution:
select m.*,
(select count(*) from trans1 t where t.user_id = m.user_id) as cnt1,
(select count(*) from trans2 t where t.user_id = m.user_id) as cnt2,
(select count(*) from trans3 t where t.user_id = m.user_id) as cnt3
from master m;
With an index on user_id in each of the trans tables, the performance should also be very good.
The more canonical solution would use left join and multiple group bys:
select m.user_id,
coalesce(t1.cnt, 0) as cnt1,
coalesce(t2.cnt, 0) as cnt2,
coalesce(t3.cnt, 0) as cnt3
from master m left join
(select t.user_id, count(*) as cnt
from trans1 t
group by t.user_id
) t1
on t1.user_id = m.user_id left join
(select t.user_id, count(*) as cnt
from trans2 t
group by t.user_id
) t2
on t2.user_id = m.user_id left join
(select t.user_id, count(*) as cnt
from trans3 t
group by t.user_id
) t3
on t3.user_id = m.user_id;
The first version is easier to write and might even have better performance under most circumstances.