Join 3 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join 3 Tables

Author

Topic

sdhusodo
Starting Member

4 Posts

Posted - 2011-10-30 : 00:57:47

I have the following three tables:
InvoiceLine:
InvNo ItemNo Quantity Month Date
INV1005 105 10 01/31/11
INV1006 106 10 01/31/11
INV1001 101 10 01/31/11
INV1001 102 10 01/31/11
INV1002 101 10 01/31/11
INV1003 101 10 02/28/11
INV1004 102 10 02/28/11

ReturnLine:
RetNo No_ Quantity Month Date
CR1001 101 2 01/31/11
CR1002 101 2 01/31/11
CR1003 104 2 01/31/11
CR1004 103 2 02/28/11
CR1005 105 2 02/28/11

Item:
No_ Category Brand Description
100 Tennis MI MIKASA 01
101 Tennis MI MIKASA 02
102 Tennis MI MIKASA 20
103 Tennis MI MIKASA 03
104 Tennis AS ASANO 04
105 Tennis AS ASANO 05
106 Tennis AS ASANO 06

I want to join these tables to get the following result:
Month Date Category Brand Sales Qty Return Qty
01/31/11 Tennis AS 20 2
01/31/11 Tennis MI 30 4
02/28/11 Tennis AS 0 2
02/28/11 Tennis MI 20 2

The closest query that I could get is:

SELECT SIL.[Month Date],ITM.[Category],ITM.[Brand]
,SUM(SIL.[Quantity]) [Sales Qty],SUM(SCL.[Quantity]) [Return Qty]

FROM [InvoiceLine] SIL
JOIN [Item] ITM
on SIL.[No_] = ITM.[No_]
JOIN (
SELECT [Month Date],ITM.[Category],ITM.[Brand],sum([Quantity]) [Return Qty]
FROM [ReturnLine]
GROUP BY [Month Date],ITM.[Category],ITM.[Brand]
) SCL
ON
SCL.[Month Date] = SIL.[Month Date]
and SCL.[No_] = ITM.[No_]

GROUP BY SIL.[Month Date],ITM.[Category],ITM.[Brand]

and it does not give me the correct result.
The sales qty are correct but the return qty is wrong, and I am missing the return for AS for 02/28/11:

Month Date Category Brand Sales Qty Return Qty
01/31/11 Tennis AS 20 4
01/31/11 Tennis MI 30 12
02/28/11 Tennis MI 20 4

Please help. Thank you.

sdhusodo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 01:28:07

[code]
SELECT r.[Month Date],i.Category,i.Brand,SUM(r.Sales) AS SalesQty,SUM(r.Return) AS ReturnQty
FROM Item i
INNER JOIN (SELECT ItemNo,[Month Date],SUM(Sales) AS Sales,SUM(Return) AS return
FROM
(SELECT ItemNo,[Month Date],Quantity AS Sales, 0 AS Return
FROM InvoiceLine
UNION ALL
SELECT No_,[Month Date],0,Quantity
FROM ReturnLine
)t
GROUP BY ItemNo,[Month Date]
)r
ON r.ItemNo = i.[No_]
GROUP BY i.Category,i.Brand,r.[Month Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/