SQL Server : query to get table data (count) from another table as VIEW

Question

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?

Answer 1

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.