I believe that MySQL 4.1 supports nested queries and is now out of beta.


-----Original Message-----
From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
Andrew Nagy
Sent: Monday, November 29, 2004 5:20 PM
To: [log in to unmask]
Subject: Re: [CODE4LIB] SQL question

Andrew Darby wrote:

> Is there a way, in the SQL, to generate my "mama bear" set: i.e., with
> the subjects grouped together like so:
> Smith, Joe | English, History, Italian | 425-5000 | [log in to unmask]
> Turner, Ted | Math, Politics, Zoology | 425-5111 | [log in to unmask]
> Or do I have to run another query inside the php loop?  (I seem to
> remember, from a previous life, that you could do these "queries
> queries" in ColdFusion.)

Andrew, you cannot do this with MySQL.  MySQL does not support nested
queries nor union clauses.  You could attempt this with Postgres, but
you still wouldn't be able to have multiple columns from one table all
in one column in a result from another table.  The best way to do this
is to use 2 queries if you are using MySQL.  If you want to use postgres
you could do this with a stored procedure or a really advanced nested
query but then the nested query would probably run slower than 2 simple
queries. :)

Good luck