If you know the subjects beforehand

select name,
       concat(if(count(if(subj = 'English',  1, NULL)) = 1, 'English,',  ''),
              if(count(if(subj = 'History',  1, NULL)) = 1, 'History,',  ''),
              if(count(if(subj = 'Math',     1, NULL)) = 1, 'Math,',     ''),
              if(count(if(subj = 'Politics', 1, NULL)) = 1, 'Politics,', ''),
              if(count(if(subj = 'Zoology',  1, NULL)) = 1, 'Zoology',   '')) as subjects
  from staff,
       subject
 where staff.staff_id = subject.staff_id
 group by name


But that's just for fun.
It is better to use your php as others have suggested.

Leif
======================================
Leif Andersson, Systems Librarian
Stockholm University Library


-----Ursprungligt meddelande-----
Från: Code for Libraries [mailto:[log in to unmask]]För Andrew
Darby
Skickat: den 29 november 2004 22:18
Till: [log in to unmask]
Ämne: [CODE4LIB] SQL question


Hello, all.  Based on the pretty SQL I saw in some recent posts, I
thought I would fall on the mercy of the crowd, and see if anyone could
help . . .

I'm trying to display (using MySQL/PHP) a list of subject librarians and
their associated subjects, in a neat table.  This gets me close:

SELECT lname, fname, title, tel, email, supersubject FROM supersubs,
staff WHERE supersubs.staff_id = staff.staff_id GROUP BY lname

except only the first associated subject is delivered.

The following query gets me all the data I need, but with all the
librarian info duplicated:

SELECT lname, fname, title, tel, email, supersubject FROM supersubs,
staff WHERE supersubs.staff_id = staff.staff_id GROUP BY lname, supersubject

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.)

Thanks, and I hope this doesn't qualify as a dumb question . . .

Andrew Darby