Hi guys, this is an SQL question and don't know which type of JOIN, GROUP BY etc. to use, it is for a chat program where messages are related to rooms and each day in a room is linked to a transcript etc.

Basically, when outputting my transcripts, I need to show which users have chatted on that transcript. At the moment I link them through the messages like so:

SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, long 
 FROM room_transcripts 
  JOIN rooms ON room_transcripts.room=rooms.id 
  JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date 
  JOIN users ON transcript_users.user=users.id 
 WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2) 
 ORDER BY room_transcripts.id DESC, long ASC

The result set looks like this:

Array
(
    [0] => Array
        (
            [id] => 2
            [name] => Room 2
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Jerry Seinfeld
        )

    [1] => Array
        (
            [id] => 1
            [name] => Room 1
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Jerry Seinfeld
        )

    [2] => Array
        (
            [id] => 1
            [name] => Room 1
            [niceDate] => Wednesday, April 14
            [date] => 2010-04-14
            [long] => Test Users
        )

)

I would like though for each element in the array to represent one transcript entry and for the users to be grouped in an array as the entry's element. So 'long' will be an array listing all the names. Can this be done?

At the moment I just append the names and when the transcript date and room changes I echo them retrospectively, but I will do the same for files and highlighted messages and it's messy.

Thanks.