is it possible?

[Pasquale]

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

[Aggro]

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.

[Bill Karwin]

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

See http://dev.mysql.com/doc/mysql/en/group-by-functions.html.

Regards,
Bill K.

[Pasquale]

Bill Karwin wrote:
> 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().
>

Is there an alternative to GROUP_CONCAT() for MySQL 4.0.23?

[Bill Karwin]

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.

Regards,
Bill K.