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?

@experimentX, 1 row in `metadata` may be related to `n` rows in `content`. I want to structure the query so that it returns `5n` rows, not 5 rows — Mr Hyde, Feb 26 '11 at 11:43
@Shakti Singh thats what i am veering to, just thought there could be a more direct way — Mr Hyde, Feb 26 '11 at 11:48

score 17 · Accepted Answer · answered Feb 26 '11 at 12:10

17

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

answered Feb 26 '11 at 12:10

Christian Payne

7,081
5
38
59

2

This will get the first 5 rows of 'metadata' and then do further querying on that limited dataset. – Mazatec Oct 24 '12 at 12:13
@Ash - yes, that's what he asked. "I want to limit the left table metadata to 5" – Christian Payne Oct 30 '12 at 22:40

score 0 · Answer 2 · answered Aug 15 '14 at 22:40

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 isn't tested. Please let me know if you run into issue implementing this - and I can elaborate.

S L · Answer 3 · 2011-02-26T12:23:30.523

0

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

edited Feb 26 '11 at 12:23

answered Feb 26 '11 at 12:02

S L

14,262
17
77
116

Tried that! Not what I want. A left join will incude any records in `left` table that dont have counterparts in the right table. An inner join will include only those records common to both – Mr Hyde Feb 26 '11 at 12:10
Though I don't recommend mine – S L Feb 26 '11 at 12:24

MySQL Inner Join With LIMIT to left table

3 Answers3

Linked