|
Home > Archive > MySQL Server Forum > June 2005 > is it possible?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Pasquale 2005-06-16, 3:23 am |
| 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 2005-06-16, 11:23 am |
| 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 2005-06-16, 11:23 am |
| 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(DISTINC
T 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/g...functions.html.
Regards,
Bill K.
| |
| Pasquale 2005-06-29, 3:23 am |
|
Bill Karwin wrote:
> Pasquale wrote:
>
>
>
> 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(DISTINC
T 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/g...functions.html.
>
> Regards,
> Bill K.
| |
| Bill Karwin 2005-06-29, 8:23 pm |
| 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.
|
|
|
|
|