0

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!

5
  • What is the joining key? i.e. how do you 'bolt' one query to another? What makes the first row of one query join to the first row of another? – Mark Sinkinson Jan 20 '15 at 14:28
  • this is the problem... the only thing that links them is the ProdID M0000044 where there is 6 rows for each. Why i'm getting 36 rows in the cross join query... – jhowe Jan 20 '15 at 14:36
  • 1
    Then how can you join one to another? How do you know which rows should join? You can't join them and guarantee they are correct... – Mark Sinkinson Jan 20 '15 at 14:38
  • i don't know i've never come across this concept before. The records are all in the same tables only separated by different statuses. is there no way of joining datasets but not evaluating for every row? – jhowe Jan 20 '15 at 14:44
  • 2
    If you need to eliminate rows from the join, you need to define how SQL Server should know which rows to include, and which rows to exclude. – Aaron Bertrand Jan 20 '15 at 14:46
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.