left join problem

Question

left join problem

asked

tal raz 21

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

Guoxiong 7,681 Reputation points

2020-09-04T16:17:54.633+00:00

You may change the second "LEFT JOIN" to "INNER JOIN".
Ronen Ariely 13,776 Reputation points Microsoft MVP

2020-09-04T16:30:42.313+00:00

Please provide queries to create the tables and to insert sample data (this should come instead of your stories about the tables)

According to the sample data, please provide image or description of the expected result you want to get
tal raz 21 Reputation points

2020-09-05T02:26:13.553+00:00

here you go.

CREATE TABLE #PART(PART INT, PARTNAME VARCHAR(20));
CREATE TABLE #WARHSBAL(WARHS INT, PART INT, BALANCE INT);
CREATE TABLE #WAREHOUSES(WARHS INT, WARHSNAME VARCHAR(20));

INSERT INTO #PART VALUES(1, '001'), (2, '002'), (3, '003')
INSERT INTO #WARHSBAL VALUES(10, 1, 200), (20, 2, 30), (30, 2, 30), (40, 1, 30)
INSERT INTO #WAREHOUSES VALUES(10, 'Main'), (20, 'Flr'), (30, 'Park'), (40, 'Q-ME')

DESIRE RESUALT based on above query

PARTNAME BALANCE
001 50
002 30
003 0

Guoxiong 7,681

2020-09-05T04:16:31.027+00:00

Try this:

SELECT P.PARTNAME, SUM(ISNULL(T.BALANCE, 0))
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;

tal raz 21 Reputation points

2020-09-05T15:01:51.567+00:00

you are a genies, man. thanks.
didn't know about derived table. just reading about it.
Guoxiong 7,681 Reputation points

2020-09-05T16:59:08.933+00:00

If that solved the issue, please mark it as an answer. It would be helpful to other users who has the same issue and read this thread. Thanks.
Guoxiong 7,681 Reputation points

2020-09-05T20:49:50.51+00:00

Not sure if you picked the correct response as the answer. I reposted the script based on the code in the comments.

Guoxiong 7,681 Reputation points

2020-09-04T16:17:54.633+00:00

You may change the second "LEFT JOIN" to "INNER JOIN".
Ronen Ariely 13,776 Reputation points Microsoft MVP

2020-09-04T16:30:42.313+00:00

Please provide queries to create the tables and to insert sample data (this should come instead of your stories about the tables)

According to the sample data, please provide image or description of the expected result you want to get
tal raz 21 Reputation points

2020-09-05T02:26:13.553+00:00

here you go.

CREATE TABLE #PART(PART INT, PARTNAME VARCHAR(20));
CREATE TABLE #WARHSBAL(WARHS INT, PART INT, BALANCE INT);
CREATE TABLE #WAREHOUSES(WARHS INT, WARHSNAME VARCHAR(20));

INSERT INTO #PART VALUES(1, '001'), (2, '002'), (3, '003')
INSERT INTO #WARHSBAL VALUES(10, 1, 200), (20, 2, 30), (30, 2, 30), (40, 1, 30)
INSERT INTO #WAREHOUSES VALUES(10, 'Main'), (20, 'Flr'), (30, 'Park'), (40, 'Q-ME')

DESIRE RESUALT based on above query

PARTNAME BALANCE
001 50
002 30
003 0
Guoxiong 7,681 Reputation points

2020-09-05T04:16:31.027+00:00

Try this:

SELECT P.PARTNAME, SUM(ISNULL(T.BALANCE, 0)) 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;
tal raz 21 Reputation points

2020-09-05T15:01:51.567+00:00

you are a genies, man. thanks.
didn't know about derived table. just reading about it.
Guoxiong 7,681 Reputation points

2020-09-05T16:59:08.933+00:00

If that solved the issue, please mark it as an answer. It would be helpful to other users who has the same issue and read this thread. Thanks.
Guoxiong 7,681 Reputation points

2020-09-05T20:49:50.51+00:00

Not sure if you picked the correct response as the answer. I reposted the script based on the code in the comments.

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;

left join problem

1 additional answer

Activity