is it possible?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Pasquale
    #1

    is it possible?

    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,f name,lname,even tname FROM
    ((((participant as p
    LEFT OUTER JOIN regpartrel as rp ON p.participantID =rp.relparticip antID)
    LEFT OUTER JOIN registration as r ON
    rp.relregistrat ionID=r.registr ationID)
    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

    Tags: None
    • Aggro
      #2
      Re: is it possible?

      Pasquale wrote:[color=blue]
      > 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.[/color]

      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.

        Comment

        • Bill Karwin
          #3
          Re: is it possible?

          Pasquale wrote:[color=blue]
          > 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.[/color]

          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(DI STINCT e.eventname SEPARATOR ', ') AS event_list
          FROM ((((participant as p
          LEFT OUTER JOIN regpartrel as rp
          ON p.participantID =rp.relparticip antID)
          LEFT OUTER JOIN registration as r
          ON rp.relregistrat ionID=r.registr ationID)
          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/gr...functions.html.

          Regards,
          Bill K.

            Comment

            • Pasquale
              #4
              Re: is it possible?



              Bill Karwin wrote:[color=blue]
              > Pasquale wrote:
              >[color=green]
              >> 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.[/color]
              >
              >
              > If you use MySQL 4.1.6 or higher, MySQL has a grouping function
              > GROUP_CONCAT().
              >[/color]

              Is there an alternative to GROUP_CONCAT() for MySQL 4.0.23?
              [color=blue]
              > 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(DI STINCT e.eventname SEPARATOR ', ') AS event_list
              > FROM ((((participant as p
              > LEFT OUTER JOIN regpartrel as rp
              > ON p.participantID =rp.relparticip antID)
              > LEFT OUTER JOIN registration as r
              > ON rp.relregistrat ionID=r.registr ationID)
              > 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/gr...functions.html.
              >
              > Regards,
              > Bill K.[/color]

                Comment

                • Bill Karwin
                  #5
                  Re: is it possible?

                  Pasquale wrote:[color=blue]
                  > Is there an alternative to GROUP_CONCAT() for MySQL 4.0.23?[/color]

                  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.

                    Comment

                    Previous template Next
                    Working...