Get multiple rows from a table to per row of another table

Question

I have a problem here, I wonder if it is possible to do this in just one query instead of having the server do several queries.

So here's the thing, what I want is, in each category, to search all stores belonging to that category within a limit of 24 stores. That is, imagining that I have 5 categories registered, I will search 24 stores belonging to that category, which in the conclusion I will have a total of 120 stores in the query result.

The following code is an example, but this will only fetch the first 24 stores of any category without iterating through each category.

SELECT *
FROM categories c
    LEFT JOIN (SELECT * FROM stores_categories LIMIT 24 OFFSET 0) sc ON sc.id_category = c.id
WHERE type = 'STORE' OR type = 'ALL';

Someone sent me this and it is very similar to my problem, How to SELECT the newest four items per category?, It is kind of like that, but I would like to be able to limit and with offset to make pages and not just the first 24 most recent in each category.

The example code from the link I was given:

SELECT sc1.*
FROM stores_categories sc1
    LEFT OUTER JOIN stores_categories sc2 ON (sc1.id_category = sc2.id_category AND sc1.id_store < sc2.id_store)
GROUP BY sc1.id_store
HAVING COUNT(*) < 24
ORDER BY sc1.id_category;

And the database server which one I'm is the version 5.5.64-MariaDB. An fiddle example where is possible to make tests with same values from my database. https://www.db-fiddle.com/f/jcowJL9S4FQXqKg2yMa8kf/0

Answer 1

It could make use of a calculated row_number per category.

--
-- Emulated row_number via variables
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, rn
FROM
(
  SELECT sc.*
  , @rn := CASE 
           WHEN @cat_id = sc.id_category
           THEN @rn + 1 ELSE 1
           END AS rn
  , @cat_id := sc.id_category as cat_id
  FROM stores_categories sc
  CROSS JOIN (SELECT @cat_id:=0, @rn:=0) vars
  ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC
) sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
WHERE rn BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC, sc.id DESC

Or

--
-- Grouped self-join
-- 
SELECT sc.id, sc.id_store
, cat.name AS cat_name
, cat.type AS cat_type
, COUNT(sc2.id_store) AS cnt
FROM stores_categories sc
LEFT JOIN stores_categories sc2
  ON sc2.id_category = sc.id_category 
 AND sc2.id_store >= sc.id_store
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
GROUP BY sc.id
HAVING cnt BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC;

Or

--
-- Correlated sub-query
-- 
SELECT sc.id, id_store
, cat.name AS cat_name
, cat.type AS cat_type
, ( select count(*)
    from stores_categories sc2
    where sc2.id_category = sc.id_category 
      and sc2.id_store >= sc.id_store
  ) as rn
FROM stores_categories AS sc
LEFT JOIN categories cat 
  ON cat.id = sc.id_category
HAVING rn BETWEEN 1 AND 24
ORDER BY sc.id_category, sc.id_store DESC;

Demo on db<>fiddle here