mySQL - Limit the number of rows returned in one side of JOIN statement? -

mySQL - Limit the number of rows returned in one side of JOIN statement? -

everyone,

i curious if there way sort of limiting query on mysql database:

here tables:

events event_id event_title creation_time images image_id src event_id comments event_comment_id event_comment event_id

i fetch events sorted creation time, , 3 images , 3 comments each event.

any help, resources, or criticism welcome. give thanks you

here's 1 approach. basically, rownumber associated each grouping of comments/images , display 3:

select e.*, max(case when i.rn = 1 i.image_id end) image1, max(case when i.rn = 2 i.image_id end) image2, max(case when i.rn = 3 i.image_id end) image3, max(case when c.rn = 1 c.event_comment_id end) comment1, max(case when c.rn = 2 c.event_comment_id end) comment2, max(case when c.rn = 3 c.event_comment_id end) comment3 events e left bring together (select @currow:=if(@prevrow = event_id, @currow + 1, 1) rn, image_id, src, event_id, @prevrow:= event_id images bring together (select @currow := 0) r ) on e.event_id = i.event_id left bring together (select @currow2:=if(@prevrow2 = event_id, @currow2 + 1, 1) rn, event_comment_id, event_comment, event_id, @prevrow2:= event_id comments bring together (select @currow2 := 0) r ) c on e.event_id = c.event_id grouping e.event_id order e.event_id, e.creation_time desc

and here sql fiddle.

mysql