Last post May 27, 2017 12:47 PM by mgebhard
May 27, 2017 12:06 PM|acmedeepak|LINK
I am working on an Invoice summary query. I have multiple queries which need to be merged into one to get the accurate result. Please help me merge these queries into one.
My Queries are:
select ItemNumber, SellingPrice from PJ_Item_M where Variation = 'Std' order by ItemNumber
select ItemPrice from PJ_Daily_DbnMatrix_T where ItemNumber = '" & RSItmInfo(0) & "' and Distbn_Date = '" & bShpDate & "' and ItemPrice is Not NULL
select Load_Priority,Store_Number,Location from PJ_Location_M order by Load_Priority,Store_Number
(select ItemNumber, SUM(CAST([" & ResRep(1) & "] as Integer)) from PJ_Matrix_RowDstbn_T where Distbn_Date = '" & bShpDate & "' and ItemNumber = '" & ItemId(j) & "' group by ItemNumber) Union (select ItemNumber,SUM(CAST(Qty as Integer)) from PJ_Special_Order_T where Store_Number = '" & ResRep(1) & "' and SplOrd_Date = '" & bShpDate & "' and ItemNumber = '" & ItemId(j) & "' Group by ItemNumber)
--Total Special order value
select sum(CAST(Price as decimal(10,2)) * Qty) from PJ_Special_Order_T where SplOrd_Date = '" & bShpDate & "'
I am new to SQL server. Please help me put these queries into one to get the desired output.
May 27, 2017 12:47 PM|mgebhard|LINK
I suspect you are asking how to JOIN tables. It looks like you can use ItemNumber to JOIN PJ_Item_M, PJ_Daily_DbnMatrix_T, PJ_Special_Order_T, and PJ_Matrix_RowDstbn_T
FROM PJ_Item_M AS i
INNER JOIN PJ_Daily_DbnMatrix_T AS m ON i.ItemNumber = m.ItemNumber
INNER JOIN PJ_Location_M AS l ON i.ItemNumber = l.ItemNumber
INNER JOIN PJ_Special_Order_T AS o ON i.ItemNumber = o.ItemNumber
INNER JOIN PJ_Matrix_RowDstbn_T AS r ON i.ItemNumber = r.ItemNumber
The SELECT * will return all fields from each table so you'll need to add the fields you want. Secondly, I have no idea how the tables are designed. So you might have to do a LEFT JOIN rather than an INNER JOIN if it is possible that ItemNumber does not exist
in one of the tables.
SQL joins reference for more information on JOINs