Skip to main content
Database Administrators

Return to Question

If we just want the games user haven't played
Source Link
Hari Shankar
Hari Shankar
  • 3
  • 2
+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Need for Speed   |  1 |    3   |   t    |
| Far Cry          |  2 |    2   |   t    |
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

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

+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+
added 350 characters in body
Source Link
Hari Shankar
Hari Shankar
  • 3
  • 2

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

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

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;

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

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

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 |
+------------------+----+--------+--------+
| Need for Speed   |  1 |    3   |   t    |
| Far Cry          |  2 |    2   |   t    |
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

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;
Source Link
Hari Shankar
Hari Shankar
  • 3
  • 2

Using Select Alias in where clause

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 3 haven't played and also the number of times the game was totally played.

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 = ? 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 ?