MySQL Inner Join With LIMIT to left table

Question

I have this database query

SELECT *
FROM (`metadata` im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC
LIMIT 5, 5 

The query limits the total rows in the result to 5. I want to limit the left table metadata to 5 without limiting the entire result-set.

How should I write the query?

Answer 1

If you think about what you are trying to do, you're not really doing a select against metadata.

You need to sub query that first.

Try:

SELECT *
FROM ((select * from metadata limit 5) im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC