How can I group three tables into one table?

byMR

July 6, 2022

Table 1-ApplicationUser
UserID UserName

1——–user1
2——–user2

Table 2-UserShip

UserShipID UserID ShıpID
1———–1——1
2———–2——1

Table 3 -UserCar

UserCarID UserID CarID
1———1——–1
2———2———2

I have 3 tables and I want to display these three tables as follows.

UserName – Car COUNT – SHIPCOUNT
user1—– 1 ————1

I want to list the username, the number of cars and ships he owns. What sql code should I write? I couldn’t group it somehow, can you help me?

>Solution :

You might be looking for something like this:

SELECT
  ApplicationUser.UserID,
  ApplicationUser.UserName,
  COUNT(DISTINCT CarID) AS OwnedCars,
  COUNT(DISTINCT ShipID) AS OwnedShips
FROM
  ApplicationUser
  LEFT JOIN UserCar ON UserCar.UserID = ApplicationUser.UserID
  LEFT JOIN UserShip ON UserShip.UserID = ApplicationUser.UserID
GROUP BY
  ApplicationUser.UserID, ApplicationUser.UserName