Grouping records with SQL

[bostjank]

Hi!

I have 3 tables in my database and would like to show the records in a "fancy" table:

EVENTS

EventID EventName EventType
------- --------- ---------
1 Excel Seminar
2 Meeting Internal
3 Word Seminar
... ... ...

PARTICIPANTS

EventID ParticipantID
------- -------------
1 12
1 75
1 1
1 6
1 8
2 12
2 6
2 45
3 12
3 1
3 8
3 24
... ...


PARTICIPANT_INFO

ParticipantID Name Surname
------------- ---- -------
1 Jim Nelson
2 Tom Grandy
3 Susan Bell
4 Mark Peterson
... ... ...

What I would like to be able to do is to show a table of all participants of a certain EventType, where each participant would only be listed once, but with the list of all events he participated at. Something like this:

Name Surname Events
---- ------- -----------
Jim Nelson Excel, Word
Ton Grandy Excel, PowerPoint
... ... ...

I have managed to show each participant only once with GROUP BY, but I don't know how to list events. This is my current SQL statement

SELECT participant_info.ParticipantID, Max(paricipant_info.Name) As MName,
Max(participant_info.Surname) As MSurname,
Max(event.EventName) As MEvent
FROM events LEFT JOIN (paricipants LEFT JOIN participant_info ON
participants.ParticipantID = participants_info.ParticipantID) ON
events.EventID = participants.ParticipantID WHERE events.EventType = 'Seminar'
GROUP BY participant_info.ParticipantID

I would also like to know how to count records with GROUP BY.
Do you have any solution?

Thanks,
Bostjan

[raf]

bostjank,

should have done a search so your problem would have been solved instantly.:D

http://www.codingforums.com/showthread.php?s=&threadid=12577&highlight=grouping