Adding column to select statement brings in all historical data

Question

Good evening all!

I'm running into a really odd issue that I'm having trouble understanding.

I have 3 tables (parts table, parts move history and a parts detail table).

What I'm trying to do is have the result set return lot#,part#,product description,quantity,part location, what's currently in inventory (versus full history) and who last moved the product.

Now, for the query. When I run the below query, I get a result set of 4,751 rows; which lines up perfectly with my expected results. However, when I try to add in the userid field, I then get a result set of 186,573. This large result set appears to pull in all historic data versus just matching the userid to the 4,751 rows I actually need.

From the Parts Table I need (prod_desc) From the Parts Detail Table I need (lot,part#,lotquantity,prtlocation) From the Parts Move History Table I need (move_date,user_id)

4,751 Query:

SELECT DISTINCT
inv.lot,
inv.part#,
prt.prod_desc,
inv.lotquantity,
inv.prtlocation,
MAX(mv.move_date)AS 'Move Date'
FROM invdet AS inv
LEFT JOIN movetable AS mv ON inv.part# = mv.part#
LEFT JOIN partmstr AS prt ON inv.part# = prt.part#
WHERE inv.lot IS NOT NULL
GROUP BY inv.lot,inv.part#,prt.prod_desc,inv.lotquantity,inv.prtlocation
ORDER BY inv.prtlocation

186,573 Query:

SELECT DISTINCT
inv.lot,
inv.part#,
prt.prod_desc,
inv.lotquantity,
inv.prtlocation,
MAX(mv.move_date)AS 'Move Date'
mv.user_id
FROM invdet AS inv
LEFT JOIN movetable AS mv ON inv.part# = mv.part#
LEFT JOIN partmstr AS prt ON inv.part# = prt.part#
WHERE inv.lot IS NOT NULL
GROUP BY inv.lot,inv.part#,prt.prod_desc,inv.lotquantity,inv.prtlocation,mv.user_id
ORDER BY inv.prtlocation

If I don't use the MAX function, I do not get current inventory and instead get all results in the table, which I do not need. I'm still learning and my GROUP BY's leave a lot to be desired as I'm still wrapping my head around it (open to suggestions!). I'm sure there's a subquery I can throw in here somewhere, but I'm still figuring those out as well. Any help is greatly appreciated!

score 0 · Accepted Answer · answered Mar 02 '19 at 22:15

I think the problem is that when you insert mv.user_id from table movetable you get all part's movements and not only the last one with date max(mv.move_date). One way is to remove the left join to movetable and use maybe a cross apply like

SELECT inv.lot,inv.part,prt.prod_desc,inv.lotquantity,inv.prtlocation,x.move_date,x.user_id
FROM invdet AS inv
CROSS APPLY(SELECT TOP 1
             mv.user_id,mv.move_date
            FROM movetable mv
            WHERE inv.part=mv.part
            ORDER BY mv.move_date DESC) AS x
LEFT JOIN partmstr AS prt ON inv.part=prt.part
WHERE inv.lot IS NOT NULL
ORDER BY inv.prtlocation

I've not tested it but should be fine, maybe a bit slow because cross apply executes one subquery per each row in inv table. If it is too slow, you can user ROWNUMBER to create a table composed of only the last movements and then use it in the LEFT JOIN, as follows

SELECT inv.lot,inv.part,prt.prod_desc,inv.lotquantity,inv.prtlocation,y.move_date,y.user_id
FROM invdet AS inv
LEFT JOIN(SELECT x.user_id,x.move_date,x.part
          FROM (SELECT mv.user_id,mv.move_date,mv.part,rn=ROWNUMBER() OVER(PARTITION BY mv.part ORDER BY mv.move_date DESC)
                FROM movetable mv) AS x
          WHERE x.rn=1) AS y ON y.part=inv.part
LEFT JOIN partmstr AS prt ON inv.part=prt.part
WHERE inv.lot IS NOT NULL
ORDER BY inv.prtlocation

Hope it helps.

The CROSS APPLY worked! Thank you very much. I've never worked with CROSS APPLY before, so I'll have to do some research on that and ROWNUMBER to really understand what it's doing. Thank you again! — Drezriel, Mar 02 '19 at 22:58

Adding column to select statement brings in all historical data

1 Answers1