title: Left-joining the same table multiple times
body: <p>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:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>[The two times tracked in the games table are the start and end time]</p>
  
  <p>Here is some dummy data to populate the tables:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Now, I need to produce a report in the following format:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>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. :-)</p>
  
  <p>I started with this:</p>
  
  <pre><code>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 &lt; ug2.uid and
  ug2.gid = ug3.gid and
  ug2.uid &lt; ug3.uid and
  ug3.gid = ug4.gid and
  ug3.uid &lt; ug4.uid
  </code></pre>
  
  <p>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.</p>
  
  <p>This is my second attempt:</p>
  
  <pre><code>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 &lt; ug3.uid
  left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid &lt; ug4.uid
  where
  g.id = ug1.gid and
  ug1.gid = ug2.gid and
  ug1.uid &lt; ug2.uid
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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 &lt; ug3.uid
  left join users_games ug4 on ug3.gid = ug4.gid and ug3.uid &lt; 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 &lt; ug2.uid
  </code></pre>
  
  <p>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?</p>
  
  <p>This appears to me should be a solved problem in other contexts. Will appreciate all help here.</p>