Question about query - can this be done?

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Question about query - can this be done?

On Tue, Jun 2, 2009 at 11:52 AM, Ray wrote:
> Hello,
>
> I've tried the manual and google, but I am not even sure what to call wha=
t I
> want to do.
>
> simplified data example:
> I have a table of start and end times for an event, and an id for that ev=
ent
> in a table. each event may occur multiple times, but never more than 5 ti=
mes
> and rarely more than 3.
> I want a query that will provide one record per event with all times incl=
uded.
> feel free to answer RTFM or STFW as long as you provide the manual sectio=
n or
> key words. =A0;)
> Thanks,
> Ray
>
>
> chart form follows:
>
> id | event_id | start | end
> ---------------------------------------
> 1 =A0| =A0 =A04 =A0 =A0 =A0 =A0 | t1 =A0 =A0| t2
> 2 =A0| =A0 =A04 =A0 =A0 =A0 =A0 | t3 =A0 =A0| t4
> 3 =A0| =A0 =A04 =A0 =A0 =A0 =A0 | t5 =A0 =A0| t6
> 4 =A0| =A0 =A05 =A0 =A0 =A0 =A0 | t1 =A0 =A0| t2
> 5 =A0| =A0 =A05 =A0 =A0 =A0 =A0 | t3 =A0 =A0| t4
>
> becomes
>
> id | event_id | start | end | start | end | start | end
> ------------------------------------------------------------ -------------=
--
> ? =A0| =A0 =A0 4 =A0 =A0 =A0 =A0| t1 =A0 =A0| t2 =A0 =A0 | t3 =A0 =A0| t4=
=A0 =A0| =A0t5 =A0 =A0| t6
> ? =A0| =A0 =A0 5 =A0 =A0 =A0 =A0| t1 =A0 =A0| t2 =A0 =A0 | t3 =A0 =A0| 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_dat=
es
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 ) end=
2,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 3 ), ',',
-1 ) start3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 3 ), ',', -1 ) end=
3,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 4 ), ',',
-1 ) start4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 4 ), ',', -1 ) end=
4,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(start), ',', 5 ), ',',
-1 ) start5,
SUBSTRING_INDEX( SUBSTRING_INDEX(GROUP_CONCAT(end), ',', 5 ), ',', -1 ) end=
5
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Question about query - can this be done?

--------------060702090005080507050304
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

-----

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

--------------060702090005080507050304--

Mysql 4.1 vs 5.0

QmVuY2hpbmcNClNvbWVib2R5IGtub3dzIHdoeSA0LjEgaXMgZmFzdGVyIHRo YW4gNS4wIG15c3Fs
IHZlcnNpb25zDQoNCkluZy4gSmFpbWUgRnVlbnRlcyBSLg0KOTk3NTAwNDU5 DQoyNDIxOTA1LTI0
MjMyNTINCg0KRW52aWFkbyBkZXNkZSBtaSAgQmxhY2tCZXJyeSBkZSBDbGFy by4NCg0KLS0tLS1P
cmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IEJyZW50IEJhaXNsZXkgPGJy ZW50dGVjaEBnbWFp
bC5jb20+DQoNCkRhdGU6IFR1ZSwgMiBKdW4gMjAwOSAxMjozMjozOSANClRv OiBSYXk8cmF5QHN0
aWxsdGVjaC5uZXQ+DQpDYzogPG15c3FsQGxpc3RzLm15c3FsLmNvbT4NClN1 YmplY3Q6IFJlOiBR
dWVzdGlvbiBhYm91dCBxdWVyeSAtIGNhbiB0aGlzIGJlIGRvbmU/DQoNCg0K T24gVHVlLCBKdW4g
MiwgMjAwOSBhdCAxMTo1MiBBTSwgUmF5IDxyYXlAc3RpbGx0ZWNoLm5ldD4g d3JvdGU6DQo+IEhl
bGxvLA0KPg0KPiBJJ3ZlIHRyaWVkIHRoZSBtYW51YWwgYW5kIGdvb2dsZSwg YnV0IEkgYW0gbm90
IGV2ZW4gc3VyZSB3aGF0IHRvIGNhbGwgd2hhdCBJDQo+IHdhbnQgdG8gZG8u DQo+DQo+IHNpbXBs
aWZpZWQgZGF0YSBleGFtcGxlOg0KPiBJIGhhdmUgYSB0YWJsZSBvZiBzdGFy dCBhbmQgZW5kIHRp
bWVzIGZvciBhbiBldmVudCwgYW5kIGFuIGlkIGZvciB0aGF0IGV2ZW50DQo+ IGluIGEgdGFibGUu
IGVhY2ggZXZlbnQgbWF5IG9jY3VyIG11bHRpcGxlIHRpbWVzLCBidXQgbmV2 ZXIgbW9yZSB0aGFu
IDUgdGltZXMNCj4gYW5kIHJhcmVseSBtb3JlIHRoYW4gMy4NCj4gSSB3YW50 IGEgcXVlcnkgdGhh
dCB3aWxsIHByb3ZpZGUgb25lIHJlY29yZCBwZXIgZXZlbnQgd2l0aCBhbGwg dGltZXMgaW5jbHVk
ZWQuDQo+IGZlZWwgZnJlZSB0byBhbnN3ZXIgUlRGTSBvciBTVEZXIGFzIGxv bmcgYXMgeW91IHBy
b3ZpZGUgdGhlIG1hbnVhbCBzZWN0aW9uIG9yDQo+IGtleSB3b3Jkcy4goDsp DQo+IFRoYW5rcywN
Cj4gUmF5DQo+DQo+DQo+IGNoYXJ0IGZvcm0gZm9sbG93czoNCj4NCj4gaWQg fCBldmVudF9pZCB8
IHN0YXJ0IHwgZW5kDQo+IC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLQ0K
PiAxIKB8IKAgoDQgoCCgIKAgoCB8IHQxIKAgoHwgdDINCj4gMiCgfCCgIKA0 IKAgoCCgIKAgfCB0
MyCgIKB8IHQ0DQo+IDMgoHwgoCCgNCCgIKAgoCCgIHwgdDUgoCCgfCB0Ng0K PiA0IKB8IKAgoDUg
oCCgIKAgoCB8IHQxIKAgoHwgdDINCj4gNSCgfCCgIKA1IKAgoCCgIKAgfCB0 MyCgIKB8IHQ0DQo+
DQo+IGJlY29tZXMNCj4NCj4gaWQgfCBldmVudF9pZCB8IHN0YXJ0IHwgZW5k IHwgc3RhcnQgfCBl
bmQgfCBzdGFydCB8IGVuZA0KPiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCj4gPyCg fCCgIKAgNCCgIKAg
oCCgfCB0MSCgIKB8IHQyIKAgoCB8IHQzIKAgoHwgdDQgoCCgfCCgdDUgoCCg fCB0Ng0KPiA/IKB8
IKAgoCA1IKAgoCCgIKB8IHQxIKAgoHwgdDIgoCCgIHwgdDMgoCCgfCB0NA0K Pg0KDQpJIHRoaW5r
IHdoYXQgeW91IGFyZSBsb29raW5nIGZvciBpcyBHUk9VUF9DT05DQVQuIFlv dSBjYW4ganVzdCBH
Uk9VUA0KQlkgZXZlbnQgaWQsIGFuZCB0aGVuIHByb2Nlc3MgdGhlIHJlc3Vs dGluZyBkZWxpbWl0
ZWQgc3RyaW5nIG9uIHRoZQ0KZnJvbnQgZW5kLg0KU0VMRUNUIGV2ZW50X2lk LCBHUk9VUF9DT05D
QVQoc3RhcnQpIHN0YXJ0X2RhdGVzLCBHUk9VUF9DT05DQVQoZW5kKSBlbmRf ZGF0ZXMNCkZST00g
ZXZlbnRzIEdST1VQIEJZIGV2ZW50X2lkDQoNCk9yIGV2ZW4gY29tYmluZWQg c3RhcnQgYW5kIGVu
ZCBkYXRlcyBpbnRvIGEgc2luZ2xlIHN0cmluZyBhbmQgZ3JvdXAgdGhlbS4N ClNFTEVDVCBldmVu
dF9pZCwgR1JPVVBfQ09OQ0FUKCBDT05DQVQoc3RhcnQsICctJywgZW5kKSAp IHN0YXJ0X2VuZA0K
RlJPTSBldmVudHMgR1JPVVAgQlkgZXZlbnRfaWQNCg0KQnV0LCBpZiB5b3Ug cmVhbGx5IHdhbnQg
dG8gZ2V0IGl0IGluIHRoZSBjb2x1bW4gZm9ybWF0IHlvdSBpbmRpY2F0ZSwN CnlvdSBjYW4gbWFr
ZSBhIG11Y2ggbW9yZSBjb21wbGljYXRlZCBxdWVyeS4gVXNlIFNVQlNUUklO R19JTkRFWCB0bw0K
c3BsaXQgb3V0IHRoZSBwYXJ0cyBvZiB0aGUgZ3JvdXAgeW91IG5lZWQuDQoN ClNFTEVDVCBldmVu
dF9pZCwNClNVQlNUUklOR19JTkRFWChHUk9VUF9DT05DQVQoc3RhcnQpLCAn LCcsIDEgKSBzdGFy
dDEsDQpTVUJTVFJJTkdfSU5ERVgoR1JPVVBfQ09OQ0FUKGVuZCksICcsJywg MSApIGVuZDEsDQpT
VUJTVFJJTkdfSU5ERVgoIFNVQlNUUklOR19JTkRFWChHUk9VUF9DT05DQVQo c3RhcnQpLCAnLCcs
IDIgKSwgJywnLA0KLTEgKSBzdGFydDIsDQpTVUJTVFJJTkdfSU5ERVgoIFNV QlNUUklOR19JTkRF
WChHUk9VUF9DT05DQVQoZW5kKSwgJywnLCAyICksICcsJywgLTEgKSBlbmQy LA0KU1VCU1RSSU5H
X0lOREVYKCBTVUJTVFJJTkdfSU5ERVgoR1JPVVBfQ09OQ0FUKHN0YXJ0KSwg JywnLCAzICksICcs
JywNCi0xICkgc3RhcnQzLA0KU1VCU1RSSU5HX0lOREVYKCBTVUJTVFJJTkdf SU5ERVgoR1JPVVBf
Q09OQ0FUKGVuZCksICcsJywgMyApLCAnLCcsIC0xICkgZW5kMywNClNVQlNU UklOR19JTkRFWCgg
U1VCU1RSSU5HX0lOREVYKEdST1VQX0NPTkNBVChzdGFydCksICcsJywgNCAp LCAnLCcsDQotMSAp
IHN0YXJ0NCwNClNVQlNUUklOR19JTkRFWCggU1VCU1RSSU5HX0lOREVYKEdS T1VQX0NPTkNBVChl
bmQpLCAnLCcsIDQgKSwgJywnLCAtMSApIGVuZDQsDQpTVUJTVFJJTkdfSU5E RVgoIFNVQlNUUklO
R19JTkRFWChHUk9VUF9DT05DQVQoc3RhcnQpLCAnLCcsIDUgKSwgJywnLA0K LTEgKSBzdGFydDUs
DQpTVUJTVFJJTkdfSU5ERVgoIFNVQlNUUklOR19JTkRFWChHUk9VUF9DT05D QVQoZW5kKSwgJywn
LCA1ICksICcsJywgLTEgKSBlbmQ1DQpGUk9NIGV2ZW50cyBHUk9VUCBCWSBl dmVudF9pZDsNCg0K
SSB0aGluayB0aGF0IHdpbGwgZ2l2ZSB0aGUgZm9ybWF0IHlvdSBzcGVjaWZp ZWQsIGJ1dCBJIGFt
IG5vdA0KcmVjb21tZW5kaW5nIHlvdSBkbyBpdCB0aGlzIHdheS4NCg0KSG9w ZSB0aGF0IGhlbHBz
Lg0KDQpCcmVudCBCYWlzbGV5DQoNCi0tDQpNeVNRTCBHZW5lcmFsIE1haWxp bmcgTGlzdA0KRm9y
IGxpc3QgYXJjaGl2ZXM6IGh0dHA6Ly9saXN0cy5teXNxbC5jb20vbXlzcWwN ClRvIHVuc3Vic2Ny
aWJlOiAgICBodHRwOi8vbGlzdHMubXlzcWwuY29tL215c3FsP3Vuc3ViPWpm dWVudGVzQHNlZ3Vy
c2F0LmNvbQ0KDQo=

Re: Question about query - can this be done?

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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=gcdmg-mysql-2@m.gmane.org

RE: Question about query - can this be done?

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=3Dtmp.id


Hope that helps.


Regards,
Nathan Sullivan

-----Original Message-----
From: Ray [mailto:ray@stilltech.net]=20
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 t=
he
> 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=20

> events.event_id=3Devent_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, '-=
',=20
end) ) start_end FROM events GROUP BY event_id;=20
SELECT * FROM event_details JOIN v ON events.event_id=3Devent_details.event=
_id=20

Thanks,=20
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 tha=
t
> > > 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


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dnsullivan@cappex.co=
m


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

[solved]Re: Question about query - can this be done?

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@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=nsullivan@cappex.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org