List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:June 2 2009 4:32pm
Subject:Re: Question about query - can this be done?
View as plain text  
On Tue, Jun 2, 2009 at 11:52 AM, Ray <ray@stripped> 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
>

I think what you are looking for is GROUP_CONCAT. You can just GROUP
BY event id, and then process the resulting delimited string on the
front end.
SELECT event_id, GROUP_CONCAT(start) start_dates, GROUP_CONCAT(end) end_dates
FROM events GROUP BY event_id

Or even combined start and end dates into a single string and group them.
SELECT event_id, GROUP_CONCAT( CONCAT(start, '-', end) ) start_end
FROM events GROUP BY event_id

But, if you really want to get it in the column format you indicate,
you can make a much more complicated query. Use SUBSTRING_INDEX to
split out the parts of the group you need.

SELECT event_id,
SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 1 ) start1,
SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 1 ) end1,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 2 ), ',',
-1 ) start2,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 2 ), ',', -1 ) end2,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',',
-1 ) start3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',',
-1 ) start4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',',
-1 ) start5,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end5
FROM events GROUP BY event_id;

I think that will give the format you specified, but I am not
recommending you do it this way.

Hope that helps.

Brent Baisley
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