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:r...@stilltech.net] > Sent: Tuesday, June 02, 2009 4:58 PM > To: mysql@lists.mysql.com > 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=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
