How to select rows from first table & matching entry in another table?

Question

I have below scenario, Where I want to fetch all distinct records from first table & matching records from second & for non matching show 0,

Table1
id  group
1   a
2   b
3   c
4   a
5   b

Table1 having group data,

Table2
m_id    group   Available  Flag
1         a      100       A
2         a      200       A
2         b      100       A
3         b      150       A
3         c      280       A
4         a      -50       D
4         b      20        D

Table2 having items data available by group wise,

I want groups list with items available with Flag=A or not, Desired output,

m_id    group   Available
1        a       100
1        b       0
1        c       0
2        a       200
2        b       100
2        c       0
3        a       0
3        b       150
3        c       280

I have tried this through left join but It not gives desired output.

select t2.M_ID,t1.GROUP,t2.Available 
    from #temp as t1
    left join #temp2 as t2 on t1.GROUP=t2.GROUP AND t2.flag='A'
    group by t2.M_ID,t1.GROUP,t2.Available

Output is,

  M_ID  GROUP   Available
   1    a        100
   2    a        200
   2    b        100
   3    c        280
   3    b        150

Please suggest me for desired output.

Answer 1

Use cross join to generate all the rows and then left join to bring in the values:

select m.M_ID, g.GROUP, coalesce(t2.Available, 0) as Available
from #temp g cross join
     (select t2.m_id
      from #temp2 t2
      where flag = 'A'
      group by t2.m_id
     ) m left join
     #temp2 t2
     on t2.GROUP = g.GROUP and t2.m_id = m.m_id;

Based on your sample data, you have no duplicates in #temp2 so aggregation is not needed. Of course, if you do have duplicates, it is easy enough to do the aggregation.

Answer 2

    create table Table1 (id int, [group] char(1))
    insert table1 values(1,'a'),(2,'b'),(3,'c')

    create table Table2 (m_id int, [group] char(1), Available int)
    Insert Table2 values (1,'a',100),(2,'a',200),(2,'b',100),(3,'b',150),(3,'c',280)

    select distinct t2_1.m_id, t1.[group], isnull(t2_2.Available,0) as Available
    from Table2 t2_1 cross join table1 t1
    left join Table2 t2_2 on t2_1.m_id=t2_2.m_id and t2_2.[group]=t1.[group]