MySQL Inner Join With LIMIT To Left Table

Question

I have a 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 complete rows of the result. 5. I want to limit the left metadata table to 5 without restricting the entire result set.

How to write a request?

Answer 1

If you are thinking about what you are trying to do, you are not really opting against metadata .

First you need to execute the sub request.

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 

Answer 2

Well, I think you mean LEFT JOIN try using LEFT JOIN instead of INNER JOIN

  SELECT DISTINCT * 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 

Answer 3

Here is another possible approach:

 SET @serial=0; SET @thisid=0; SELECT @serial := IF((@thisid != im.id), @serial + 1, @serial), @thisid := im.id, * FROM (`metadata` im) WHERE `im`.`id` = '00039' AND `current_revision` = 1 AND @serial < 5 ORDER BY `timestamp` DESC 

This is not verified. Please let me know if you have any problems with this, and I can clarify.