Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    The sunny side of the Alps
    Posts
    230
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Grouping records with SQL

    Hi!

    I have 3 tables in my database and would like to show the records in a "fancy" table:
    Code:
    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:
    Code:
    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
    PHP Code:
    SELECT participant_info.ParticipantIDMax(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.ParticipantIDON 
    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

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    bostjank,

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

    http://www.codingforums.com/showthre...light=grouping


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •