> I have problem with this syntax: > SELECT game_id FROM game WHERE game_id NOT IN (SELECT game_id FROM player WHERE user_id!=$id_user) > > I need select games where user is not connected. I have two tables game(game_id, ....) and player(player_id, game_id, user_id, ...). In table player are many players in one game.
SELECT `game_id` FROM `game` AS t1 LEFT JOIN `player` AS t2 ON t1.`game_id` = t2.`game_id` AND t2.`user_id` = $id_user WHERE t2.`game_id` IS NULL will get you the game_id's of all games where there is not a corresponding record for $id_user in the player table. A few comments: - the LEFT JOIN results in all records from the `game` table plus all correspondig data from the `player` table. if there is not corresponding data from the `player` table, NULL values will be used in the result set - using the user_id=$id_user in the ON-clause will use this condition as a criterium for the join and not for the selection of the result set. - because of the extra NULL values in the result you can use WHERE `game_id` IS NULL to find the records which have no real data in the player table. Hope this helps, Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
