3

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.

Let's overview simple example.

Assuming there two tables:

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `sub_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `child` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `parent_id` INT NOT NULL,
  PRIMARY KEY (`id`));

With the following content:

INSERT INTO `items` (`parent`) VALUES ('Main item1');
INSERT INTO `items` (`parent`) VALUES ('Main item2');
INSERT INTO `items` (`parent`) VALUES ('Main item3');

INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item11', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item12', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item13', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item21', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item22', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item23', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item24', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item31', '3');

Now let's create query something like this:

SELECT i.*, si.child, si.parent_id FROM items as i 
LEFT JOIN sub_items as si
ON i.id = si.parent_id;

The result will be:

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'1', 'Main item1', 'Child Item13', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'2', 'Main item2', 'Child Item23', '2'
'2', 'Main item2', 'Child Item24', '2'
'3', 'Main item3', 'Child Item31', '3'

Now the challenge is to limit join table rows (in this example limit is 2 rows):

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'3', 'Main item3', 'Child Item31', '3'

If we try to use query bellow to achieve desired outcome MySQL will throw error:

SELECT i.*, si.child, si.parent_id FROM items as i 
LEFT JOIN (SELECT * FROM sub_items WHERE parent_id = i.id LIMIT 2) as si
ON i.id = si.parent_id;

Then what is alternative approach to get result described above with 2 (or more) limit rows for each left join subquery?

6
  • What version of MySQL are you using? Jun 6, 2019 at 11:40
  • I'm not sure that you need LEFT join, I think INNER is enough.
    – Akina
    Jun 6, 2019 at 12:07
  • @Lennart MySql 5.6.40
    – Arsenius
    Jun 6, 2019 at 12:15
  • Obtain non-filtered result on the most inner subquery. Emulate ROW_NUMBER() on non-filtered result in the middle subquery using user-defined variables. Strict the result by calculated row number values in outer query.
    – Akina
    Jun 6, 2019 at 12:27
  • Welcome to the site BTW, +1 for posting create table statements, and insert statements for sample data Jun 6, 2019 at 12:43

2 Answers 2

8
+50

What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table:

SELECT i.*, si.child, si.parent_id 
FROM items as i 
LEFT JOIN LATERAL (SELECT *
                   FROM sub_items 
                   WHERE parent_id = i.id 
                   LIMIT 2) as si
    ON i.id = si.parent_id;

Concider adding an ORDER BY to your sub-select.

Other ways to do something similar is to use a window function such as:

row_number() over (partition by ... order by ...) as rn

and filter rn at the outer level.

Window functions are however only supported in MySQL 8, but you can mimic them using variables:

SELECT i.*, si.child, si.parent_id, si.rn 
FROM items as i 
LEFT JOIN (
    SELECT c.*, 
        @row_number:=CASE WHEN @parent_id = parent_id
                          THEN @row_number + 1
                          ELSE 1
                     END AS rn,      
        @parent_id := parent_id
    FROM sub_items as c
    CROSS JOIN (select @row_number := 1) as x
    CROSS JOIN (select @parent_id := -1) as y
    ORDER BY parent_id
) as si
    ON i.id = si.parent_id
    AND si.rn <= 2
;

The idea is to order the sub-query by parent_id, increment row_num by 1 for each row, and as soon as parent_id changes, reset row_num to 1.

You can read more about the idea at for example: mysql-row_number

8
  • 1
    I'm using MySql 5.6.40. Could you please provide example with window function? (using data described above)
    – Arsenius
    Jun 6, 2019 at 12:14
  • @Arsenius, window functions are not supported in 5.6 either. I'll add an example for 5.6 in a while Jun 6, 2019 at 12:16
  • Thanks a lot for such informative and useful answer. I'm a bit unclear about this code CROSS JOIN (select @row_number := 1) as x CROSS JOIN (select @parent_id := -1) as y for what is stands for?
    – Arsenius
    Jun 6, 2019 at 12:47
  • The purpose of the x and y table is to initialize the variables "@"row_number and "@"parent_id. CROSS JOIN is SQL-lingo for CARTESIAN PRODUCT. Say for example that you have A={1,2} and B={'A','B','C'}, then A CROSS JOIN B will be {(1,'A'),(1,'B'),(1,'C'),(2,'A'),...,(3,'C')}, all in all 6 tuples (2x3). Since there will always be exactly 1 row in x and y respectively we can safely CROSS JOIN them with c. You may have seen a construction like A JOIN B ON 1=1, it is the same thing. Jun 6, 2019 at 12:56
  • LATERAL and CTEs are not available in 5.6.
    – Rick James
    Jun 6, 2019 at 14:49
2

Another method that works even in ancient versions (eg 5.1) is to mimic the LATERAL join with what I call "poor man's" LATERAL JOIN / OUTER APPLY.

This is the LATERAL join we want to mimic:

SELECT i.*, si.child, si.parent_id 
FROM items AS i 
    LEFT JOIN LATERAL
      ( SELECT s.*
        FROM sub_items AS s
        WHERE s.parent_id = i.id 
        ORDER BY s.id             -- assuming we don't want arbitrary results
        LIMIT 2
      ) AS si
    ON TRUE ;     -- this is redundant: ON i.id = si.parent_id ;

The poor man's version:

SELECT i.*, si.child, si.parent_id 
FROM items AS i 
    LEFT JOIN sub_items AS si
    ON  si.parent_id = i.id
    AND si.id <=                     -- 1. three places where 
      COALESCE(
      ( SELECT s.id                  -- 2. same column is used 
        FROM sub_items AS s
        WHERE s.parent_id = i.id 
        ORDER BY s.id                -- 3. as this ORDER BY 
        
        LIMIT 1 OFFSET 1             -- pick the 2nd.
                                     -- if you want LIMIT 5, 
                                     -- you need LIMIT 1 OFFSET 4 here
      ), 
      4294967295)                    -- this would depend on the type
                                     -- of the column used in ORDER BY
                                     -- and on ASC/DESC
 ;

Test at dbfiddle.uk

Disadvantages:

  • complicated to write (and read/understand), especially when the LIMIT wanted is 2 or larger.

Advantages:

  • works everywhere - even in ancient versions.
  • adequate performance, when the number of distinct items (the parent table) is small to medium.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.