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