On June 2, 2009 10:44:48 am Peter Brawley wrote:
> Ray,
>
> >I want a query that will provide one record per event with all times
> > included. feel free to answer RTFM or STFW as long as you provide the
> > manual section or key words. ;)
>
> Can be done with a pivot table. Examples under "Pivot tables" at
> http://www.artfulsoftware.com/infotree/queries.php. If you get stuck,
> pipe up.
>
> PB
>
Thanks Peter and Brent.
GROUP_CONCAT does exactly what I want.
Brent, you're right, I don't really want to break up the times into separate
fields that bad, the results are going into PHP so I can parse the combined
fields there without much difficulty.
The next problem is how do I use the results in a join. My first thought (that
doesn't work) was:
SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end FROM events
GROUP BY event_id JOIN event_details WHERE
events.event_id=event_details.event_id
I have tried brackets, and a few other things, but I haven't got it yet.
Thanks,
Ray
> -----
>
> Ray wrote:
> > Hello,
> >
> > I've tried the manual and google, but I am not even sure what to call
> > what I want to do.
> >
> > simplified data example:
> > I have a table of start and end times for an event, and an id for that
> > event in a table. each event may occur multiple times, but never more
> > than 5 times and rarely more than 3.
> > I want a query that will provide one record per event with all times
> > included. feel free to answer RTFM or STFW as long as you provide the
> > manual section or key words. ;)
> > Thanks,
> > Ray
> >
> >
> > chart form follows:
> >
> > id | event_id | start | end
> > ---------------------------------------
> > 1 | 4 | t1 | t2
> > 2 | 4 | t3 | t4
> > 3 | 4 | t5 | t6
> > 4 | 5 | t1 | t2
> > 5 | 5 | t3 | t4
> >
> > becomes
> >
> > id | event_id | start | end | start | end | start | end
> > -------------------------------------------------------------------------
> >-- ? | 4 | t1 | t2 | t3 | t4 | t5 | t6 ? |
> > 5 | t1 | t2 | t3 | t4
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > No virus found in this incoming message.
> > Checked by AVG - www.avg.com
> > Version: 8.5.339 / Virus Database: 270.12.50/2150 - Release Date:
> > 06/02/09 06:47:00
