Joining 4 separate tables with count()-CodePudding

I'm new to SQL and i've been stuck with this problem. I have 4 tables. I've filled them with some mock information.

Games Table
|ID |Name      |Price |
|---|----------|------|
|1  |TestGame1 |2500  |
|2  |TestGame2 |1500  |
|3  |TestGame3 |3500  |

User Table
|ID |Username |Email               |
|---|---------|--------------------|
|1  |TestUser1|[email protected]|
|2  |TestUser2|[email protected]|
|3  |TestUser3|[email protected]|

UserOwnsGame Table
|GameID |UserID |
|-------|-------|
|1      |1      |
|2      |2      |
|1      |2      |
|3      |1      |
|2      |1      |

Review Table
|GameID |UserID |Rating |Comment                          |LastEdit  |
|-------|-------|-------|---------------------------------|----------|
|1      |1      |5.0    |I love this game                 |2022-04-19|
|1      |2      |4.5    |Came short of a 5.0              |2022-04-19|
|2      |2      |2.7    |Above average but nothing special|2022-04-19|

I want to scan through the data on all tables using a single query and get a table like the following,

GameID	UserID	Username	UserReviewCount	UserGameCount	Rating	Comment	LastEdit
1	1	TestUser1	2	3	5.0	I love this game	2022-04-19
1	2	TestUser1	1	2	4.5	Came short of a 5.0	2022-04-19
2	2	TestUser2	1	2	2.7	Above average but nothing special	2022-04-19

I want it for all reviews in the review table. I've tried multiple times. I can figure out ways to get the data on seperate queries. I can't figure out how to combine it all into one table like this. Especially considering the count().

Here;

UserReviewCount - Number of reviews user has made. Count on Review table.
UserGameCount - Number of games user owns. Count on UserOwnsGame table.

I've been stuck on this for one or two days now. Thank you for your help!

CodePudding user response：

We can use a sub-query to count the number of games owned. We could have used another sub-query to count the number of reviews but, as we are already using the table, it is easier to use the window function count() over.

create table Games(ID int,Name varchar(10),Price int);
insert into Games values(1,'TestGame1',2500  ),(2,'TestGame2',1500  ),(3,'TestGame3',3500  );
create table Users (ID int, Username varchar(10),Email varchar(25));
insert into Users values(1,'TestUser1','[email protected]'),(2,'TestUser2','[email protected]'),(3,'TestUser3','[email protected]');
create table UserOwnsGame (GameID int, UserID int);
insert into UserOwnsGame values(1,1),(2,2),(1,2),(3,1),(2,1);
create table Review (GameID int,UserID int,Rating decimal(3,2),Comment varchar(50),LastEdit  date);
insert into Review values(1,1,5.0,'I love this game','2022-04-19'),(1,2,4.5,'Came short of a 5.0','2022-04-19'),(2,2,2.7,'Above average but nothing special','2022-04-19');

select 
  r.GameID,
  u.ID,
  u.Username,
  count(r.GameID) over (partition by r.UserID)
    as UserReviewCount,
  uog.number_games UserGamescount,
  r.Rating,
  r.Comment,
  r.LastEdit
from
Users u
join Review r 
on u.ID = r.UserID
join (select UserID,count(GameID) number_games
      from UserOwnsGame
      group by UserID) uog
on u.ID = uog.UserID;

GameID | ID | Username  | UserReviewCount | UserGamescount | Rating | Comment                           | LastEdit  
-----: | -: | :-------- | --------------: | -------------: | -----: | :-------------------------------- | :---------
     1 |  1 | TestUser1 |               1 |              3 |   5.00 | I love this game                  | 2022-04-19
     1 |  2 | TestUser2 |               2 |              2 |   4.50 | Came short of a 5.0               | 2022-04-19
     2 |  2 | TestUser2 |               2 |              2 |   2.70 | Above average but nothing special | 2022-04-19

db<>fiddle here