I have 2 tables in SQL Server 2008 and I want to get the details from those 2 tables using join.
T-1 : vwHardwareConsolidate
I want to get
id, task_name, sum(qty), task_id from
Masterid, Act_Qty from
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)
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
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.