Left-joining the same table multiple times

Question

Suppose I have a game that can be played by 2, 3 or 4 players. I track such a game in my database (MySQL 5.1) in three tables, given below. I am hoping that the fields are self-explanatory:

create table users (id int, login char(8));
create table games (id int, stime datetime, etime datetime);
create table users_games (uid int, gid int, score int);

[The two times tracked in the games table are the start and end time]

Here is some dummy data to populate the tables:

insert into games values
(1, '2011-12-01 10:00:00', '2011-12-01 13:00:00'),
(2, '2011-12-02 11:00:00', '2011-12-01 14:00:00'),
(3, '2011-12-03 12:00:00', '2011-12-01 15:00:00'),
(4, '2011-12-04 13:00:00', '2011-12-01 16:00:00');

insert into users_games values
(101, 1, 10),
(102, 1, 11),
(101, 2, 12),
(103, 2, 13),
(104, 2, 14),
(102, 3, 15),
(103, 3, 16),
(104, 3, 17),
(105, 3, 18),
(102, 4, 19),
(104, 4, 20),
(105, 4, 21);

Now, I need to produce a report in the following format:

gid     p1    p2    p3    p4  started ended
1      101   102               [g1]    [g1]
2      101   103   104         [g2]    [g2]
3      102   103   104   105   [g3]    [g3]
4      102   104   105         [g4]    [g4]

That is, a report that shows all the players who played a game in the same row. I also need their scores and some other information from the users table, but that is phase 2. :-)

I started with this:

select g.id, g.stime, g.etime, ug1.uid, ug2.uid, ug3.uid, ug4.uid
from games g, users_games ug1, users_games ug2, users_games ug3, users_games ug4
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid and
ug2.gid = ug3.gid and
ug2.uid < ug3.uid and
ug3.gid = ug4.gid and
ug3.uid < ug4.uid

This gives me all games where all four seats were occupied (ie, only game ID 3 in the above dummy data). But that is only a subset of the data I need.

This is my second attempt:

select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
    ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from ( games g, users_games ug1, users_games ug2 )
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid

This gives me 14 rows with the above dummy data. I tried to eliminate one source of error by anchoring ug1 to the entry for the lowest-UID player:

select g.id, g.stime, g.etime, ug1.uid, ug2.uid,
    ifnull(ug3.uid, ''), ifnull(ug4.uid, '')
from
( games g, users_games ug1, users_games ug2,
    (select gid as g, min(uid) as u from users_games group by g) as xx
)
left join users_games ug3 on ug2.gid = ug3.gid and ug2.uid < ug3.uid
left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid < ug4.uid
where
g.id = xx.g and
ug1.uid = xx.u and
g.id = ug1.gid and
ug1.gid = ug2.gid and
ug1.uid < ug2.uid

Now I am down to 9 rows, but I still have a lot of spurious data. I can see the problem - that for example in game 3, with ug1 anchored to user 102, there are still three players to whom ug2 can be anchored. And so on. But I cannot figure out a way to solve this conundrum - how can I ultimately achieve a query that will output 4 rows with the players in the correct order and number?

This appears to me should be a solved problem in other contexts. Will appreciate all help here.

Answer 1

SELECT games.*,
IF(min(ifnull(ug1.uid,9999999))=9999999,null,ug1.uid) AS user1,
IF(min(ifnull(ug2.uid,9999999))=9999999,null,ug2.uid) AS user2,
IF(min(ifnull(ug3.uid,9999999))=9999999,null,ug3.uid) AS user3,
IF(min(ifnull(ug4.uid,9999999))=9999999,null,ug4.uid) AS user4
FROM games
LEFT JOIN users_games AS ug1 ON ug1.gid=games.id
LEFT JOIN users_games AS ug2 ON ug2.gid=games.id AND ug2.uid>ug1.uid
LEFT JOIN users_games AS ug3 ON ug3.gid=games.id AND ug3.uid>ug2.uid
LEFT JOIN users_games AS ug4 ON ug4.gid=games.id AND ug4.uid>ug3.uid
GROUP BY games.id

ofcourse 9999999 should be the maximum possible user id -1. This trades the subqueries of the previous answer against a big grouping query.

Tested on MySQL 5.1 Ubuntu Lucid with your test data.

Answer 2

One problem you have is that you have no fields that describe a user as Player 1, 2, 3 or 4. Yet, you need to ensure that only one player is joined per LEFT JOIN.

If you add a "player_id" field to users_games, it becomes trivial...

SELECT
  *
FROM
  games
LEFT JOIN
  users_games      AS p1
    ON  p1.gid = games.id
    AND p1.player_id = 1
LEFT JOIN
  users_games      AS p2
    ON  p2.gid = games.id
    AND p2.player_id = 2
LEFT JOIN
  users_games      AS p3
    ON  p3.gid = games.id
    AND p3.player_id = 3
LEFT JOIN
  users_games      AS p4
    ON  p4.gid = games.id
    AND p4.player_id = 4

There are alternatives that avoid all the LEFT JOINs, but this examples serves well as it is the basis for the next step...)

If you can't add this field, it becomes more complex. (SQL Server, Oracle, etc, can proxy this player_id field using ROW_NUMBER(), MySQL can't.)

Instead, you need correlated sub-queries to identify the 'next player'.

SELECT
  *
FROM
  games
LEFT JOIN
  users_games      AS p1
    ON  p1.gid = games.id
    AND p1.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id)
LEFT JOIN
  users_games      AS p2
    ON  p2.gid = games.id
    AND p2.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p1.uid)
LEFT JOIN
  users_games      AS p3
    ON  p3.gid = games.id
    AND p3.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p2.uid)
LEFT JOIN
  users_games      AS p4
    ON  p4.gid = games.id
    AND p4.uid = (SELECT MIN(uid) FROM users_games WHERE gid = games.id AND uid > p3.uid)

EDIT JOIN free version, assuming presence of player_id field...

SELECT
  games.id,
  MAX(CASE WHEN users_games.player_id = 1 THEN users_games.uid END)   AS p1_id,
  MAX(CASE WHEN users_games.player_id = 2 THEN users_games.uid END)   AS p2_id,
  MAX(CASE WHEN users_games.player_id = 3 THEN users_games.uid END)   AS p3_id,
  MAX(CASE WHEN users_games.player_id = 4 THEN users_games.uid END)   AS p4_id
FROM
  games
LEFT JOIN
  users_games
    ON users_games.gid = games.id
GROUP BY
  games.id