List:General Discussion« Previous MessageNext Message »
From:Ray Date:June 2 2009 10:37pm
Subject:[solved]Re: Question about query - can this be done?
View as plain text  
On June 2, 2009 04:13:31 pm Nathan Sullivan wrote:
> Ray,
>
> You can use the results of a query in a join with something like:
>
> select tmp.id, t1.id
> from (some_query_selecting_id) as tmp
> join t1 on t1.id=tmp.id
>
>
> Hope that helps.
>
>
> Regards,
> Nathan Sullivan

Thanks Nathan, 
I think that completes the picture.
Just what I was looking for.
Ray

>
> -----Original Message-----
> From: Ray [mailto:ray@stripped]
> Sent: Tuesday, June 02, 2009 4:58 PM
> To: mysql@stripped
> Subject: Re: Question about query - can this be done?
>
> On June 2, 2009 03:14:36 pm Ray wrote:
> > 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
>
> not sure where this typo came from                    ^^^^
> I meant ON
>
> > 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
>
> I found a solution, but not sure if it's a good idea.
>
> CREATE OR REPLACE VIEW v AS SELECT event_id, GROUP_CONCAT( CONCAT(start,
> '-', end) ) start_end FROM events GROUP BY event_id;
> SELECT * FROM event_details JOIN v ON
> events.event_id=event_details.event_id
>
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

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