Excluding records between tables

Question

take a look to the following tables.

Table spawns:

+----------+---+----+----+--+
| id_spawn | position | map |
+----------+---+----+----+--+
|        1 |        1 |   1 |
|        2 |        2 |   1 |
+----------+----------+-----+

Table games;

+---------+-----+
| id_game | map |
+---------+-----+
|       1 |   1 |
|       2 |   1 |
|       3 |   1 |
|       4 |   1 |
|       5 |   1 |
+---------+-----+

Table warriors:

+---------+------+----------+
| id_join | game | position |
+---------+------+----------+
|       1 |    1 |        2 |
+---------+------+----------+

I am trying to get position from spawns that have not been used in warriors giving a specific game. At first attempt I did:

select s.position
from spawns s
left join games g on g.map = s.map
left join warriors w on w.game = g.id_game
where s.position not in(w.position)
and g.id_game = 1;

The query returns 1 as expected

However, for game = 5 I was expecting 2 available positions, but empty result received. I am assuming the db engine does not find a relation between games and warriors because the game has not warriors to compare. So, how can I get these positions?

Maybe I'm missing something, but I don't see `s.game` in the table ? — Zak, Jan 04 '16 at 17:52

score 2 · Accepted Answer · edited May 23 '17 at 11:44

If I'm understanding correctly, for a given gameid you want to return the positions in the spawns table that do not have corresponding positions in the warriors table? If so, here's one option using an outer join / null check.

select s.position
from spawns s
    join games g on g.map = s.map
    left join warriors w on w.game = g.id_game and s.position = w.position
where g.id_game = 1 and w.id_join is null

BTW, another common approach is to use not exists. While most databases can optimize that approach, mysql seems to have trouble with it. Here are some good posts on the matter:

Fantastic! I have noticed that you removed `not in()` function, it looks very clean query. — manix, Jan 04 '16 at 18:09

score 1 · Answer 2 · answered Jan 04 '16 at 17:58

You can use NOT EXISTS so as to exclude positions from spawns that have been used in warriors given a specific game:

SELECT s.position
FROM spawns AS s
INNER JOIN games AS g ON g.map = s.map
WHERE g.id_game = ? AND 
      NOT EXISTS (SELECT 1
                  FROM warriors AS w
                  WHERE w.game = g.id_game AND w.position = s.position)

Excluding records between tables

2 Answers2