I've searched questions but can't seem to apply to my scenario. Please see attached code below. I am trying to join two subsets of this data but am getting duplicates/too many rows please see subsets below main query and main results (i'm hoping you can understand by providing data/examples without table defs as tables are enormous) (please excuse formatting i don't know how to line everything up in this) :
IF OBJECT_ID(N'tempdb..#TRANS', N'U') IS NOT NULL
BEGIN
DROP TABLE #TRANS;
END
DECLARE @Item NVARCHAR(6) = 'AAS682' ,
@ExpiryDate AS DATETIME = '2015-01-10 00:00:00.000'
SELECT ITO.REFERENCEID ,
WP.PRODID ,
WP.WMSPALLETID ,
ITR.ITEMID ,
ITR.QTY ,
ITR.STATUSISSUE ,
ITR.STATUSRECEIPT ,
ITO.REFERENCECATEGORY ,
PT.USEBYDATE
INTO #TRANS
FROM dbo.INVENTTRANS AS ITR
JOIN INVENTDIM AS ID ON ID.INVENTDIMID = ITR.INVENTDIMID
JOIN dbo.WMSPALLET AS WP ON WP.WMSPALLETID = ID.WMSPALLETID
JOIN dbo.PRODTABLE AS PT ON PT.PRODID = WP.PRODID
JOIN dbo.INVENTTRANSORIGIN AS ITO ON ITR.INVENTTRANSORIGIN = ITO.RECID
WHERE ITO.REFERENCECATEGORY IN ( 0, 2, 8 )
AND PT.USEBYDATE = @ExpiryDate;
SELECT * FROM #TRANS
Which gives me :
REFERENCEID PRODID WMSPALLETID ITEMID QTY STATUSISSUE STATUSRECEIPT REFERENCECATEGORY USEBYDATE
M0000042 M0000042 1288390 BAS242 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000042 M0000042 1288391 BAS242 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000042 M0000042 1288392 BAS242 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000042 M0000042 1288393 BAS242 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000042 M0000042 1288394 BAS242 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000043 M0000043 1288395 BAS243 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000043 M0000043 1288396 BAS243 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000043 M0000043 1288397 BAS243 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000043 M0000043 1288398 BAS243 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000043 M0000043 1288399 BAS243 255.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000044 1288400 AAS682 168.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000044 1288401 AAS682 168.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000044 1288402 AAS682 168.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000044 1288403 AAS682 168.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000044 1288404 AAS682 168.0000000000000000 0 2 2 2015-01-10 00:00:00.000
M0000044 M0000042 1288390 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288391 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288392 BAS242 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288395 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288396 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288397 BAS243 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 2 0 0 2015-01-10 00:00:00.000
I want to join these two query result sets
WITH ACODE
AS ( SELECT *
FROM #TRANS
WHERE ITEMID = @Item
AND STATUSISSUE IN ( 1, 2 )
)
SELECT *
FROM ACODE
SELECT *
FROM #TRANS
WHERE REFERENCECATEGORY = 8
REFERENCEID PRODID WMSPALLETID ITEMID QTY STATUSISSUE STATUSRECEIPT REFERENCECATEGORY USEBYDATE
0013949 M0000044 1288400 AAS682 -32.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 2 0 0 2015-01-10 00:00:00.000
with this
REFERENCEID PRODID WMSPALLETID ITEMID QTY STATUSISSUE STATUSRECEIPT REFERENCECATEGORY USEBYDATE
M0000044 M0000042 1288390 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288391 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288392 BAS242 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288395 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288396 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288397 BAS243 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
I tried
SELECT ACODE.REFERENCEID ,
ACODE.PRODID ,
ACODE.WMSPALLETID ,
ACODE.ITEMID ,
ACODE.QTY ,
TRANS.REFERENCEID ,
TRANS.PRODID ,
TRANS.WMSPALLETID ,
TRANS.ITEMID ,
TRANS.QTY
FROM ACODE
CROSS APPLY ( SELECT REFERENCEID ,
PRODID ,
WMSPALLETID ,
ITEMID ,
QTY
FROM #TRANS
WHERE ACODE.PRODID != #TRANS.PRODID
AND REFERENCECATEGORY = 8
) TRANS
REFERENCEID PRODID WMSPALLETID ITEMID QTY REFERENCEID PRODID WMSPALLETID ITEMID QTY
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013949 M0000044 1288400 AAS682 -32.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000042 1288390 BAS242 -255.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000042 1288391 BAS242 -255.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000042 1288392 BAS242 -50.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000043 1288395 BAS243 -255.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000043 1288396 BAS243 -255.0000000000000000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 M0000044 M0000043 1288397 BAS243 -50.0000000000000000
but this gave me 6 * 6 36 rows… I want to just bolt the right query on to the end of the left query. exactly like a union
SELECT *
FROM ACODE
UNION
( SELECT *
FROM #TRANS
WHERE REFERENCECATEGORY = 8
)
REFERENCEID PRODID WMSPALLETID ITEMID QTY STATUSISSUE STATUSRECEIPT REFERENCECATEGORY USEBYDATE
0013949 M0000044 1288400 AAS682 -32.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013949 M0000044 1288402 AAS682 -168.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288400 AAS682 -35.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013951 M0000044 1288401 AAS682 -29.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013952 M0000044 1288400 AAS682 -28.0000000000000000 2 0 0 2015-01-10 00:00:00.000
0013953 M0000044 1288400 AAS682 -73.0000000000000000 2 0 0 2015-01-10 00:00:00.000
M0000044 M0000042 1288390 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288391 BAS242 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000042 1288392 BAS242 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288395 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288396 BAS243 -255.0000000000000000 2 0 8 2015-01-10 00:00:00.000
M0000044 M0000043 1288397 BAS243 -50.0000000000000000 2 0 8 2015-01-10 00:00:00.000
except the bottom 6 rows attached to the right of the query… Basically the 'B' items make up the 'A' item. Which is the purpose of this report, but all the data exists in the same tables which is what i'm really struggling with… thanks!
Best Answer
There's a simple answer to this. If the relationship between the tables does not exist to give you the results you want, you need to CREATE your own relationship. I used ROW_NUMBER OVER (ORDER BY PALLETID) to give me unique ID's for both datasets. THen simply join (make sure you check different joins to make sure you don't exclude rows from one side or the other) ACODEID = BCODEID. Simple!