Having Joins query with limit 10 on second table

Question

I have designer and design table

designer has many designs

I want to get all designers with 10 designs for each designer

Is there way I can do it in a single query in either postgresql, mysql

Answer 1

For best performance in Postgres (not possible in MySQL) use a LATERAL join:

SELECT d.designer, d1.design  -- whatever columns you need
FROM   designer d
LEFT   JOIN LATERAL (
   SELECT *                   -- or just needed columns
   FROM   design
   WHERE  designer_id = d.designer_id
   -- ORDER  BY ???           -- you may want to pick certain designs
   LIMIT  10
   ) d1 ON true
ORDER  BY d.designer_id, d.design_id;  -- or whatever

This gives you 10 designs per designer - or as many as he/she has if there are fewer.

• LEFT JOIN LATERAL ... ON true keeps designers in the result that don't have a single design (yet).

  • What is the difference between LATERAL and a subquery in PostgreSQL?
  • Call a set-returning function with an array argument multiple times

• You get best performance if you add an ORDER BY clause that matches an existing index on design like:

  CREATE INDEX foo ON design (designer_id, design_id)
  

  Then, in the subquery d1 in above query:

  ...
  ORDER  BY design_id
  ...
  

  Now Postgres can pick the top items from the index directly.

Related answer with more details:

• Limit number of rows per group from join (NOT to 1 row)

Answer 2

select * from  ( 
    Select row_number() OVER (PARTITION BY a.designer ORDER BY b.design DESC) rn,
        a.* ,b.* 
    from a
    inner join  b
        on a.id = b.id
    ) 
    where rn <= 10

mysql doesn't have window functions, which you need, so this is postgresql.

Answer 3

In MySQL, you can make use of user-defined variables to emulate some of the analytic functions available in other databases...

  SELECT v.designer_id
       , v.design_id
    FROM ( SELECT @rn := IF(r.id = @prev_r_id,@rn+1,1) AS rn
                , @prev_r_id := r.id AS designer_id
                , d.id AS design_id
             FROM (SELECT @rn := 0, @prev_r_id := NULL) i
            CROSS
             JOIN designer r
             LEFT
             JOIN design d
               ON d.designer_id = r.id
            ORDER BY r.id, d.id
         ) v 
   WHERE v.rn <= 10
   ORDER BY v.designer_id, v.design_id

You can run just the inline view query (v) to see what that returns. What it does is order the rows by designer_id, and compares the value from the current row to the value from the previous row... if they match, it increments @rn by 1, otherwise, it resets @rn to 1. Net effect is that we get an ascending integer sequence from rn for each designer_id... 1,2,3.

The outer query filters out the rows where rn is greater than 10.

If a given designer has fewer than ten designs, we get fewer than ten rows for that designer.