left join problem

Question

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

Answer 1

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'

Answer 2

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;