How can I group three tables into one table?-CodePudding

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?

CodePudding user response：

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