With the query below a participant can have more than one event to their name. Is it possible to have the results somehow group the event names to each participant or row? So, if the participant is registered for 3 events the row will have his/her name and the 3 event names, rather than 3 rows with their name and each event name.
Thanks.
SELECT participantID,fname,lname,eventname FROM ((((participant as p LEFT OUTER JOIN regpartrel as rp ON p.participantID=rp.relparticipantID) LEFT OUTER JOIN registration as r ON rp.relregistrationID=r.registrationID) LEFT OUTER JOIN subevent as se ON r.relsubeventID=se.subeventID) LEFT OUTER JOIN event as e ON se.releventID=e.eventID) WHERE fname LIKE '%brad%' GROUP BY p.participantID ORDER BY fname, lname, participantID
Pasquale wrote: > With the query below a participant can have more than one event to their > name. Is it possible to have the results somehow group the event names > to each participant or row? So, if the participant is registered for 3 > events the row will have his/her name and the 3 event names, rather than > 3 rows with their name and each event name.
With temp-tables or subqueries, that should be possible (not sure how to do that exactly). I personally do that in the application logic usually.
Pasquale wrote: > With the query below a participant can have more than one event to their > name. Is it possible to have the results somehow group the event names > to each participant or row? So, if the participant is registered for 3 > events the row will have his/her name and the 3 event names, rather than > 3 rows with their name and each event name.
If you use MySQL 4.1.6 or higher, MySQL has a grouping function GROUP_CONCAT().
I haven't used it, but from the docs I believe the usage in your case would be:
SELECT p.participantID, p.fname, p.lname, GROUP_CONCAT(DISTINCT e.eventname SEPARATOR ', ') AS event_list FROM ((((participant as p LEFT OUTER JOIN regpartrel as rp ON p.participantID=rp.relparticipantID) LEFT OUTER JOIN registration as r ON rp.relregistrationID=r.registrationID) LEFT OUTER JOIN subevent as se ON r.relsubeventID=se.subeventID) LEFT OUTER JOIN event as e ON se.releventID=e.eventID) WHERE p.fname LIKE '%brad%' GROUP BY p.participantID ORDER BY p.fname, p.lname, p.participantID
>> With the query below a participant can have more than one event to >> their name. Is it possible to have the results somehow group the event >> names to each participant or row? So, if the participant is registered >> for 3 events the row will have his/her name and the 3 event names, >> rather than 3 rows with their name and each event name.
> If you use MySQL 4.1.6 or higher, MySQL has a grouping function > GROUP_CONCAT().
Is there an alternative to GROUP_CONCAT() for MySQL 4.0.23?
> I haven't used it, but from the docs I believe the usage in your case > would be:
> SELECT p.participantID, p.fname, p.lname, > GROUP_CONCAT(DISTINCT e.eventname SEPARATOR ', ') AS event_list > FROM ((((participant as p > LEFT OUTER JOIN regpartrel as rp > ON p.participantID=rp.relparticipantID) > LEFT OUTER JOIN registration as r > ON rp.relregistrationID=r.registrationID) > LEFT OUTER JOIN subevent as se > ON r.relsubeventID=se.subeventID) > LEFT OUTER JOIN event as e > ON se.releventID=e.eventID) > WHERE p.fname LIKE '%brad%' > GROUP BY p.participantID > ORDER BY p.fname, p.lname, p.participantID
Pasquale wrote: > Is there an alternative to GROUP_CONCAT() for MySQL 4.0.23?
No, there is not. You either need to upgrade to MySQL 4.1, or else retrieve your results as you are currently doing, and then manipulate the result set in your application code.
Not everything can be done in SQL. Sometimes you just have to write some code.
|
