Reference to outer query in subquery JOIN

Question

I'm calling on for MySQL warriors here. My problem is that I need to use a reference to an outer query's table within a left outer join in an subquery. I know this is not possible, but I have no idea on how to replace the subquery filtering with the typical joins in the outer query.

I'll take a typical example to illustrate my issue.

It's the Olympic games, I have several teams (say table tennis teams) in which there are 1 to N players. A match between two teams is finished when all of its players have played against all of the opposing team's players. In brief, I have 3 tables :

Team (id, country); 
Player (id, team_id, name) 
Game (id, playerA_id, playerB_id).

I want to search all players that have disputed none or not every game against all of the opposing team's players (and who are not in my team obviously). Here is my query :

SELECT t.*, p.*
FROM player p
INNER JOIN team t ON t.id = p.team_id AND t.id != My_Team_ID
WHERE EXISTS (
    -- If an opposing team's player has played no game => game.id is NULL
    -- If he hasn't played against all of my teammates => there will be at least one row where game.id is NULL
    SELECT *
    FROM player
    INNER JOIN team ON team.id = player.team_id AND team.id = My_Team_ID
    LEFT OUTER JOIN game ON player.id IN (game.playerA_id, game.playerB_id) AND p.id IN (game.playerA_id, game.playerB_id)
    WHERE game.id IS NULL
)

I've put a dump for practical purposes : http://pastebin.com/HW3L5ukz I tried to put the 2nd condition of the LEFT OUTER JOIN in the WHERE in the subquery but it doesn't return the proper results.

Any idea on how to achieve this ? Thanks in advance

This would be easier if Game and Game_Player were separate tables. — Marcus Adams, Aug 02 '12 at 14:15
@MarcB : I tried that but it doesn't return the right results; if I type my subquery alone by replacing my p.id with players' ids, it works, but if I put « LEFT OUTER JOIN p.id IN (game.playerA_id, game.playerB_id) » as « WHERE p.id IN (game.playerA_id, game.playerB_id) », it doesn't. I made a test where one opponent has not played against everybody and another has, with the condition in the LEFT OUTER JOIN, I get my player - contrary to when it's in the WHERE. — rgandoin, Aug 02 '12 at 14:23
@MarcusAdams : it was just to simplify the problem, what would be the point to make 2 separate tables ? Game is just a linking table — rgandoin, Aug 02 '12 at 14:25
If Game_Player had game_id and player_id as columns, if you wanted to find games that a particular player was in, you could query a single column in Game_Player, instead of two columns in Game. — Marcus Adams, Aug 02 '12 at 14:41

Marcus Adams · Accepted Answer · 2012-08-02T14:49:33.780

SELECT p1.*, p2.*
FROM player p1
JOIN team t1
  ON t1.id = p1.team_id AND t1.id = My_Team_ID
LEFT JOIN player p2
  ON p2.id != p1.id
JOIN team t2
  ON t2.id = p2.team_id AND t2.id != My_Team_ID
LEFT JOIN game g1
  ON (g1.playerA_id = p1.id OR g1.playerB_id = p1.id)
  AND (g1.playerA_id = p2.id OR g1.playerB_id = p2.id)
WHERE g1.id IS NULL

If I use 1 for My_Team_ID, I get the following results, which shows the remaining matches:

id  team_id name            id  team_id     name
1   1       Laurent Dupuis  6   2           Alec Russell
2   1       Stéphane Leroy  6   2           Alec Russell
3   1       Julien le Guen  4   2           Mark Johnsson
3   1       Julien le Guen  6   2           Alec Russell

Thanks Marcus, it works like a charm. Actually my real query is a bit more complicated so I jumped into complicating even more. But with this simple example and your answer, it appears obvious. — rgandoin, Aug 02 '12 at 15:02

Reference to outer query in subquery JOIN

1 Answers1

Linked