MariaDB Query using IN with LIMIT by row

Question

I am having some difficulty trying to use LIMIT in one of my Sub Queries, I ran into the error

MariaDB doesn't yet support 'LIMIT in subquery'

On a query that is similair to what I've done in PostgreSQL in order to get 3 child results for every parent that has a child

select * from parent
join child on child.ch_pa_id = parent.pa_id
and child.ch_id in (
  select child.ch_id from child
  where child.ch_id = parent.pa_id
  limit 3
)
order by parent.pa_id;

I saw that there was a question regarding the same thing here

MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

The question has an answer but I am unable to get it work for my needs mainly because I need to do it by row with a join, I tried to apply the same thing to my query but I'm not sure how to keep the join working

select * from parent as p
join (
  select * from child
  where child.ch_pa_idno = p.pa_idno # this line breaks it
  limit 3
) as c on c.ch_pa_id = p.pa_id
order by p.pa_id;

It's giving an error that p.pa_idno is an Unknown column, I'm sure I'm being an idiot and there's something clearly wrong here

EXAMPLE

Here is a working example done with PostgreSQL http://sqlfiddle.com/#!17/4ed4d/2. It is returning only two parent records and for each parent it is returning only 2 child records

true but order doesnt matter in this case only because it's just an example — TheLovelySausage, May 20 '19 at 18:25

FanoFN · Accepted Answer · 2019-05-21T08:24:42.180

A workaround from your original query:

SELECT * FROM parent
JOIN child ON child.ch_pa_id = parent.pa_id
AND child.ch_id IN 
  (SELECT ch_id FROM (
  SELECT child.ch_id FROM child JOIN parent
  WHERE child.ch_id = parent.pa_id
  LIMIT 3) a
)
ORDER BY parent.pa_id;

Retaining the original sub-query operation to extract ch_id from child table with LIMIT 3 however by adding parent table in the join. Then perform another operation to get the sub-query result then use that for your IN.

EDIT: I've got a working query but I have mixed feeling about it:

SELECT * FROM parent 
JOIN child ON parent.pa_id=child.ch_pa_id
LEFT JOIN (
 SELECT ch_pa_id,SUBSTRING_INDEX(chid,' ',1) f,SUBSTRING_INDEX(chid,' ',-1) e 
 FROM (
  SELECT ch_pa_id,
         SUBSTRING_INDEX(GROUP_CONCAT(ch_id ORDER BY ch_id ASC SEPARATOR ' '),' ',3) chid 
         FROM child 
         GROUP BY ch_pa_id) a) b 
 ON child.ch_pa_id=b.ch_pa_id
WHERE child.ch_id 
BETWEEN f AND e;

Honestly, I'm afraid that this won't perform well in large data but I'm always learning something new and some of the function here I just discover recently.

According to your fiddle, I notice that you only take the first 3 ch_id for every parent. Hence, this SUBSTRING_INDEX(GROUP_CONCAT(ch_id ORDER BY ch_id ASC SEPARATOR ' '),' ',3) chid. GROUP_CONCAT to show data horizontally and SUBSTRING_INDEX to get the first 3 ch_id in the assembly. Normally group_concat will return value as such; 1,2,3 and substring_index can only identify first, second and so-on value separated by [space] (' '). That's why I added SEPARATOR ' '.

Then I use SUBSTRING_INDEX twice on the outer query to define f and e whereby SUBSTRING_INDEX(chid,' ',1) to get the first value and SUBSTRING_INDEX(chid,' ',-1) to get the last value from the column. And with that I join it with the original query and added BETWEEN f and e.

This is close but it only gets the Parent that is linked to the Child rows returned by the Limit 3 instead of 3 Child rows for every Parent like my PostgreSQL one does — TheLovelySausage, May 21 '19 at 06:25
wow, that definitely does seem to work but phewww... looks complex XD — TheLovelySausage, May 21 '19 at 09:21
It does! I'm still trying to find a much simpler way and I'm new to MariaDB :D — FanoFN, May 21 '19 at 09:33

score 0 · Answer 2 · answered May 17 '19 at 21:36

Please explain, in words, the desired result.

Meanwhile, see if this gives you what you want:

SELECT p.*
    FROM parent AS p
    JOIN child AS c ON c.ch_pa_idno = p.pa_idno
    ORDER BY p.pa_id
    LIMIT 3

I am confused by pa_; it sounds a lot like "parent", but apparently ch_ indicates "parent".

And what is the difference between _id and _idno?

In many implementations of parent-child (hierarchical) relations, both are in the same table. The table has PRIMARY KEY(id) and a column called parent_id Then a "selfjoin" is used to connect between parent and children.

It may also help if you provide SHOW CREATE TABLE for both parent and child.

Here is a working example done with PostgreSQL http://sqlfiddle.com/#!17/4ed4d/2, I will include it above in my question as well — TheLovelySausage, May 20 '19 at 07:58

MariaDB Query using IN with LIMIT by row

2 Answers2