How do I get values ​​from two tables in SQL Server?

advertisements

I have 2 tables in SQL Server 2008 and I want to get the details from those 2 tables using join.

T-1 : vwHardwareConsolidate

|ID|||Qty|Type|Task_Id|

T-2 :

|MasterID|Task_Id|Act_Qty|

I want to get id, task_name, sum(qty), task_id from T1 and Masterid, Act_Qty from T2

I have tried this query

select
    ID as MasterID, Task_id, Task_Name as Items,
    SUM(Qty) as Req_Qty, 0 as Act_Qty
from
    vwHardwareConsolidate
where
    type = 'Reqrd' and ID = '21'
Group by
    Task_Name,id,Task_id

union 

(select
    m.MasterID, m.Task_Id, vw.Task_Name as Items, 0 as Req_Qty, m.Act_Qty
 from
    vwHardwareConsolidate vw
 Right join
     (select
          MasterID, m.Task_Id, 0 as Req_Qty, sum(Act_qty) as Act_Qty
      from
          tbl_MaterialDistribution_Detail  m
      where
          MasterID = '21'
      group by
          m.Task_Id, MasterID) as m on m.Task_Id = vw.Task_id)

vwHardwareConsolidate

ID    Site_name Qty      Task_Name      Type

1   CITY    1   A16Port_Switch  Reqrd
1   CITY    1   Digital_Camera  Reqrd
1   CITY    1   Electronic_Pen  Reqrd

tbl_MaterialDistribution_Detail:

MasterID|TaskId|Act_qty
7   31  1
2   32  1
12  39  3


Please try this

select t1.ID, t1.Task_Name,Sum(t1.Qty) as Qty,t1.Task_Id,t2.MasterID,t2.Act_Qty
from vwHardwareConsolidate as t1
left outer join table2 as t2
on t2.Task_ID=t1.Task_ID
Group By  t1.ID, t1.Site_name, t1.Task_Name,t1.Qty,t1.Type,t1.Task_Id,t2.MasterID,t2.Act_Qty

May this will help you.

SQL Fiddle Demo