[NBLUG/talk] attn: SQL studs/studettes

[Troy Arnold]

Warning, don't read any further unless Structured Query Language really turns
you on!

So I've got this table of workshops:
mysql> desc webcastInstance;
+---------------------+------------------+------+-----+----------+
| Field               | Type             | Null | Key | Default  |
+---------------------+------------------+------+-----+----------+
| instanceID          | int(10) unsigned |      | PRI | NULL     |
| topicID             | int(10) unsigned |      | MUL | 0        |
| finalTitle          | varchar(100)     |      | MUL |          |
| webcastDate         | date             | YES  |     | NULL     |
| webcastTime         | time             | YES  |     | 00:00:00 |
| locationID          | int(10) unsigned |      | MUL | 0        |
| onsiteContactID     | int(10) unsigned |      | MUL | 0        |

along with a table of who:
mysql> desc who;
+-----------------+----------------------------------+------+-----+
| Field           | Type                             | Null | Key |
+-----------------+----------------------------------+------+-----+
| userID          | int(10) unsigned                 |      | PRI |
| userName        | varchar(10)                      |      | UNI |

joined by:
mysql> desc webcastPresenterJoin;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| instanceID  | int(10) unsigned |      | PRI | 0       |       |
| presenterID | int(10) unsigned |      | PRI | 0       |       |
+-------------+------------------+------+-----+---------+-------+

where presenterID is the userID from the who table.

Each workshop can have multiple presenters.

So a query similar to:
SELECT i.instanceID, finalTitle, userName 
FROM ((webcastInstance AS i
LEFT JOIN webcastPresenterJoin ON
i.instanceID=webcastPresenterJoin.instanceID)
LEFT JOIN who ON
who.userID=webcastPresenterJoin.presenterID)

returns something like:
+------------+-----------------------------+-----------+
| instanceID | finalTitle                  | userName  |
+------------+-----------------------------+-----------+
|          7 | Metasearching: Librarians Li| royt      |
|          3 | Want To Go Blogging?        | lba       |
|          6 | Library Privacy Audits      | kcoyle    |
|          6 | Library Privacy Audits      | mminow    |
|         13 | Privacy, Libraries and the L| mminow    |
|         31 | Services for Small Businesse| bwill     |
|         31 | Services for Small Businesse| chuck     |
|         31 | Services for Small Businesse| jeffl     |

As you can see, multiple rows per instanceID.

What I want is a query that returns something like:
+------------+-----------------------------+------------+----------+
| instanceID | finalTitle                  |  userName  |userName2 |
+------------+-----------------------------+------------+----------+
|          6 | Library Privacy Audits      |  kcoyle    |mminow    |
|         13 | Privacy, Libraries and the L|  mminow    |mminow    |
|         31 | Services for Small Businesse|  bwill     |chuck     |

Or otherwise concatenate the userName field so I get one row per instanceID.
My whole reasoning for this is that I want to be able to ORDER BY a userName,
and I want mysql to do the work rather than my PHP app.

If it helps to visualize:
http://galecia.zenux.net/infopeople.php

Any ideas?
Anyone read this far?

-troy 
(This long e-mail was made much easier by Vim's column select, ctrl-v)