Using Select Alias in where clause

Question

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 ?

Answer 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

• Hari Shankar: 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: I have edited the question, what can I do if I just wanted to have just the games that user haven't played ?

Answer 2

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;