mysql - Select first N items per relationship, one row per item -

suppose have following schema:

artists:

    +------------+------------------+------+-----+---------+----------------+     | field      | type             | null | key | default |                  |     +------------+------------------+------+-----+---------+----------------+     | id         | int(10) unsigned | no   | pri | null    | auto_increment |                   |     | name       | varchar(255)     | yes  | uni | null    |                |     +------------+------------------+------+-----+---------+----------------+ 

events:

+------------+------------------+------+-----+---------+----------------+ | field      | type             | null | key | default |              | +------------+------------------+------+-----+---------+----------------+ | id         | int(10) unsigned | no   | pri | null    | auto_increment     | | date       | timestamp        | yes  |     | null    |                | | artist_id  | int(11)          | yes  |     | null    |                | | venue_id   | int(11)          | yes  |     | null    |                | +------------+------------------+------+-----+---------+----------------+ 

assets:

+---------------------+------------------+------+-----+---------+----------------+ | field               | type             | null | key | default |          | +---------------------+------------------+------+-----+---------+----------------+ | id                  | int(10) unsigned | no   | pri | null    | | event_id            | int(11)          | yes  |     | null    |                  | source_asset_title  | varchar(255)     | yes  |     | null    |                | | source_created_time | timestamp        | yes  |     | null    |                | 

and wanted result set of 4 assets each event given artist.id, sorted event date such as:

+----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+ | event_id | asset_id | source_asset_title                                                                                                     | event_date          | date                | +----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+ |        1 |     2089 | aba                                                                                                                       | 2015-12-03 07:00:00 | 2015-12-03 07:00:00 | |        1 |     2101 | abb                                                                                                                        | 2015-12-03 07:00:00  | 2011-04-07 15:30:00 | |        1 |     2102 | abc                                                                                                                    | 2015-12-03 07:00:00  | 2011-05-22 16:00:00 | |        1 |     2107 | abd                                                                                                                     | 2015-12-03 07:00:00  | 2011-06-11 15:00:00 | |        2 |     2109 | abe                                                                                                                     | 2011-07-18 15:00:00  | 2011-07-18 15:00:00 | |        2 |     2113 | abf                                                                                                                    | 2011-07-18 15:00:00  | 2011-07-24 15:30:00 | |        2 |     2115 | abg                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-25 16:00:00 | |        2 |     2123 | abh                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-28 16:00:00 | |        3 |     2126 | abi                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-01 16:00:00 | |        3 |     2129 | abj                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-10 16:00:00 | |        3 |     2135 | abk                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-14 16:00:00 | |        3 |     2147 | abl                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-22 16:00:00 | 

how achieve without 1 subquery per event?

i believe schema , resultset here makes different enough other questions on stackexchange new question appropriate.

here sql artist.id=1, ordered newer older events.date

select   events.id event_id,   assets.id asset_id,   source_asset_title assets inner join events on events.id = assets.event_id inner join artists on artists.id = events.artist_id artists.id = 1 order events.date desc