Stupid sql error.

Question

Hello.

I have this sql statement:

SELECT game.game_id FROM game, userGame WHERE userGame.user_id = 1
AND userGame.game_id != game.game_id;

And I'm getting this:

1
1
2
2
3
3
4
4
4
5
5
5

I'm running the statement on a Sqlite3 database on Android 2.1.

The tables are:

USERGAME
--------
userId
gameId
more_columns

GAME
-----
gameId
more_columns

How can I solve this error?

UPDATE:
I want to get all game's id that they are not in usergame table when userId=1.

Thanks.

Answer 1

I'm not sure about the Sqlite syntax, but you can try something like this. Maybe someone else can straighten out the syntax?

SELECT  *
FROM    game a left outer join usergame b on a.gameId = b.gameId and b.userId = 1
WHERE   b.gameId IS NULL

OR

SELECT  *
FROM    game a, usergame b
WHERE   (a.gameId *= b.gameId AND b.userId = 1)
        AND b.gameId IS NULL

Basically, you're saying: "Give me all the games in the [game] table, but exclude the ones that userID 1 has."

I'm not sure if that's what you are trying to achieve.

Answer 2

SELECT game_id 
  FROM game a  
 WHERE NOT EXISTS (Select 1 
                     from game b, userGame c 
                    where a.game_id=b.game_id and b.game_id=c.game_id and c.user_id=1)

Answer 3

Not exists syntax:

select gameID
from game g
where not exists
(select null from usergame ug 
 where g.gameID = ug.gameID and ug.userID = 1)