CODEWITHSUNDEEP
mysql
Ian Taylor
Ian Taylor

Reputation: 357

Mysql join with limits on joined table

I am trying to return a limited of number of products per brand. The tables are

brands: id, name

products: id, brand_id, name

What I am trying to achieve is a query that will output brands.name and products_name 10 times for each brand.

I have tried joining the two tables but when it comes to applying the limit I can't see the next step. Is this possible or will I have to opt to do the brand query first and then query again on a foreach this being more processor intensive?

Upvotes: 2

Views: 48

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30849

Get 10 records per product from the second table by the following query:

SELECT * 
FROM(
    SELECT id, brand_id, name, @n := IF(@r = brand_id, @n + 1, 1) AS rownum,
    @r := brand_id
    FROM product, (SELECT @r := 0, @n := 0) a
    ORDER BY brand_id, id
    ) a
WHERE a.rownum <= 10;

And join it with brand table, e.g.:

SELECT *
FROM brand b
JOIN (SELECT * 
    FROM(
        SELECT id, brand_id, name, @n := IF(@r = brand_id, @n + 1, 1) AS rownum,
        @r := brand_id
        FROM product, (SELECT @r := 0, @n := 0) a
        ORDER BY brand_id, id
        ) a
    WHERE a.rownum <= 10
) p on b.id = p.brand_id;

Here's the SQL Fiddle.

Upvotes: 2

Related Questions