Normalizing table LIMIT issue

Question

I have 3 tables song, author, song_author.

Connection is song 1--* song_author 1--* author

I use a query like

SELECT *
FROM song
LEFT JOIN song_author ON s_id = sa_song
LEFT JOIN author ON sa_author = a_id

For the given example that would result in 3 rows:

John - How beautiful it is
John - Awesome
George - Awesome

and I am populating my objects like that so it's fine.

However, I want to add a LIMIT clause, but because it is returning several rows for just one song a LIMIT 10 doesn't always show 10 songs.

The other possibility I know is print all songs and then inside take a second query, but that would result in O(n) which I'd like to avoid.

author:
+------+--------+
| a_id | a_name |
+------+--------+
|    1 | John   |
|    2 | George |
+------+--------+

song:
+------+---------------------+
| s_id |       s_name        |
+------+---------------------+
|    1 | How beautiful it is |
|    2 | Awesome             |
+------+---------------------+

song_author:
+-----------+---------+
| sa_author | sa_song |
+-----------+---------+
|         1 |       1 |
|         1 |       2 |
|         2 |       2 |
+-----------+---------+

Answer 1

You can try something like that:

SELECT *
FROM
    (SELECT *
    FROM song
    LIMIT 10) r
LEFT JOIN song_author ON r.s_id = sa_song
LEFT JOIN author ON sa_author = a_id