List:General Discussion« Previous MessageNext Message »
From:Ray Date:June 2 2009 9:14pm
Subject:Re: Question about query - can this be done?
View as plain text  
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

Thread
Question about query - can this be done?Ray2 Jun
  • Re: Question about query - can this be done?Brent Baisley2 Jun
    • Mysql 4.1 vs 5.0Jaime Fuentes2 Jun
  • Re: Question about query - can this be done?Peter Brawley2 Jun
    • Re: Question about query - can this be done?Ray2 Jun
      • Re: Question about query - can this be done?Ray2 Jun
        • RE: Question about query - can this be done?Nathan Sullivan3 Jun
          • [solved]Re: Question about query - can this be done?Ray3 Jun