0

I have a table games

+------------------+----+
|      title       | id |
+------------------+----+
| Need for Speed   |  1 |
| Far Cry          |  2 |
| Assassin's Creed |  3 |
+------------------+----+

and I have a table games_played

+-----------+---------+
| player_id | game_id |
+-----------+---------+
|         1 |       1 |
|         1 |       2 |
|         2 |       3 |
|         2 |       1 |
|         3 |       2 |
|         3 |       1 |
+-----------+---------+

What I need here is select all the games that player_id 1 haven't played and also the number of times the game was totally played.

+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

Here is the query I wrote

select * from games, count("games_played.*"),
exists(select games_played.player_id from games_played where 
games_played.player_id = 1 and game_id = games.id) as played,
inner join games_played
on games_played.game_id = games.id
where played = false;

This is throwing and error column played does not exist.

Can someone help me here ?

1

There are several issues with the query. FROM and column expression are misplaced, "games_played.*" is not doing what it looks like, inner join instead of outer join, etc.

Try this one (I assume that (id) is the primary key of games):

select
    games.*, 
    count(games_played.*)
        as count,
    count(*) filter (where games_played.player_id = 1) > 0
        as played
from games
    left join games_played
    on games_played.game_id = games.id
group by 
    games.id
order by
    games.id ;

Test at dbfiddle.uk

  • Thanks for the query and also thanks for sharing dbfiddle.uk this, which I just came to know that something like this exists. – Hari Shankar Sep 28 '18 at 13:27
  • I have edited the question, what can I do if I just wanted to have just the games that user haven't played ? – Hari Shankar Sep 28 '18 at 13:28
0

I got the solution, using having.

select games.*, 
   count(games_played.*) as count
from games
   left join games_played
   on games_played.game_id = games.id
group by 
   games.id
having
   count(*) filter (where games_played.player_id = 1) = 0;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.