[NBLUG/talk] attn: SQL studs/studettes

[Walter Hansen]

I think this sort of thing is only possible if you use subqueries, and
then I think it's a bit of a stretch. Last I knew I was trying to upgrade
one of my MySQLs just so it could use subqueries as only the latest did
that. This is one of those things that is next to impossible in SQL, but
is darned easy in a program that reads the result. I'd probably post on on
a mysql discussion group as their should be some true SQL jedi there, but
I think you're going to end up putting query results into temorary tables
and then recombineing them or worse and I don't even remember if mysql
does that yet.

I been talking SQL to an Oracle server lately and boy is it a strange
beasty. Makes MySQL look real nice.


> 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)
>
>
> _______________________________________________
> talk mailing list
> talk at nblug.org
> http://nblug.org/cgi-bin/mailman/listinfo/talk
>
>