left join problem

asked 2020-09-04T12:44:46.343+00:00
tal raz 21 Reputation points

Hi, I have a problem in writing a query.

I have 3 tables: PART, WARHSBAL, WAREHOUSES

I'm trying to write a query that will return the sum of balance in 3 specific warehouses.

for part 004, it has no balance so I'm getting 0, so that's good.

but for part 004, i have a total balance of 343 units, but for the specific warehouses its only 40.

no matter what I do, I always get 343, instead of 40.

this is the code.

appreciate the help. thanks

SELECT P.PARTNAME, ISNULL(sum(W.BALANCE), 0)/1000
FROM PART P
LEFT JOIN WARHSBAL W ON P.PART = W.PART
LEFT JOIN WAREHOUSES WH ON WH.WARHS = W.WARHS AND WH.WARHSNAME IN ('Main', 'Park', 'Q-ME')
WHERE P.PARTNAME = '004'
GROUP BY P.PARTNAME

{count} votes

Accepted answer
  1. answered 2020-09-04T20:23:20.373+00:00
    Tom Phillips 17,511 Reputation points

    My guess is your join conditions are not valid and you are getting duplicate data or WARHSBAL includes a record for every day or something.

    I would suggest you run this and determine why you have duplicate data:

    SELECT P.PARTNAME, W.BALANCE/1000, *
    FROM PART P
    LEFT JOIN WARHSBAL W ON P.PART = W.PART
    LEFT JOIN WAREHOUSES WH ON WH.WARHS = W.WARHS AND WH.WARHSNAME IN ('Main', 'Park', 'Q-ME')
    WHERE P.PARTNAME = '004'
    
    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2020-09-05T20:47:36.673+00:00
    Guoxiong 7,681 Reputation points
    SELECT P.PARTNAME, SUM(ISNULL(T.BALANCE, 0)) AS BALANCE
    FROM PART P
    LEFT JOIN (
         SELECT W.WARHS, W.PART, W.BALANCE
         FROM WARHSBAL W
         INNER JOIN WAREHOUSES WH ON WH.WARHS = W.WARHS AND WH.WARHSNAME IN ('Main', 'Park', 'Q-ME')
    ) T ON P.PART = T.PART
    GROUP BY P.PARTNAME;
    
    No comments