On Mon, 29 Nov 2004 17:20:09 -0500, Andrew Nagy <[log in to unmask]> wrote: > 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 within > > 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. :) Actually, doing this in Postgres with a custom aggregate it should be as fast as the "group by" version you've already tried. If you get super-curious, the SQL to create the user-defined aggregate would be: CREATE OR REPLACE FUNCTION comma_concat (text,text) IMMUTABLE CALLED ON NULL INPUT RETURNS text AS 'SELECT COALESCE ($1 || '','' || $2, $2);' LANGUAGE sql; CREATE AGGREGATE comma_concat ( BASETYPE=text, SFUNC=comma_concat, STYPE=text ); Then you can do (assuming that "supersubject" is the column containing the subject): SELECT lname || ', ' || fname AS name, title, tel, email, comma_concat(supersubject) AS subjects FROM supersubs, staff WHERE supersubs.staff_id = staff.staff_id GROUP BY 1,2,3,4; -- Mike Rylander [log in to unmask] GPLS -- PINES Development Database Developer