Multiple Tables Left Join and Aggregates in SQL-CodePudding

I have a problem with my join, every time a row from the second table has more than 1 row

For this example:

tblUsers   tblUsersCars   tblUsersHouses
---------  -------------  ------------------
id         idCar          idHouse          
name       qty            qty
lastName   type           type
           idUsers        idUsers 
--------------------------------------------

        tblUsers       |    tblUsersCars   |     tblUsersHouses     |
-----------------------|-------------------|------------------------|
1, Matt, Groening      |   7, 1, red, 1    |   15, 1, "2 Floors", 1 |
2, Steve, Segal        |   8, 2, blue, 1   |                        |
3, Scarlett, Johanson  |                   |                        |
--------------------------------------------------------------------|

With these tables, every time I run my query, I expect this result:

id | name | lastName | cars | houses |
--- ------ ---------- ------ -------- 
1  | Matt | Groening |   3  |   1    |

But instead I get this result:

id | name | lastName | cars | houses |
--- ------ ---------- ------ -------- 
1  | Matt | Groening |   3  |   2    |

So what I notice is that the last column (houses) will duplicate as many times as rows are in the the column (cars) so in this example in tblUsersCars there are 2 rows and only 1 row in tblUsersHouses, but in the result the row in tblUsersHouses is duplicating once more to match tblUsersCars number of rows.

Is there any way I can fix this query no matter how many rows are in each table? I just want the sum of qty from both tables in my final query.

This is the query I'm trying to run:

SELECT 
    tblUsers.id, tblUsers.name, tblUsers.lastName,
    COALESCE(SUM(tblUsersCars.qty), 0) AS 'cars',
    COALESCE(SUM(tblUserHouses.qty), 0) AS 'houses'
FROM 
    tblUsers
LEFT JOIN 
    tblUsersCars ON tblUsersCars.idUsers = tblUsers.id
LEFT JOIN 
    tblUserHouses ON tblUserHouses.idUsers = tblUsers.id
WHERE
    tblUsers.id = 1
GROUP BY
    tblUsers.id, tblUsers.name, tblUsers.lastName

Any help will be appreciated, thanks in advance.

I solved it using @nenad aswer

CodePudding user response：

You have to make aggregated results for cars and houses per user-id, before you join across the tables. The issue in your current approach is that you have two cars, each one joined with the one house. So house entry exists for both cars. Simplified:

UserId	CarId	HouseId
1	1	1
1	2	1

So, fix can be written in many ways, but using common-table-expression is for me most readable:

;WITH CarsCTE AS
(
    SELECT idUsers, SUM(qty) as cars
    FROM tblUsersCars
    GROUP BY idUsers
),
HousesCTE AS
(
    SELECT idUsers, SUM(qty) as houses
    FROM tblUsersHouses
    GROUP BY idUsers
)
SELECT id, name, lastName, cars, houses
FROM tblUsers AS u
    LEFT OUTER JOIN CarsCTE ON CarsCTE.idUsers = u.id
    LEFT OUTER JOIN HousesCTE ON HousesCTE.idUsers = u.id
WHERE id = 1