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.

Th

mysql sql
Edit
Report