Limit JOIN query on specific table

Question

Hi there,

I'm trying to perform a select query over two tables, one containing, for example, products, and the other containing the colors that can be chosen for those products.

Each product has a different number of colors that can be chosen; some have only one color, others can have 20 of them.

The scope of the query is to parse a list of 20 products, with all of the available colors. That means the number of colors per product doesn't have to be limited, but the displayed products do.

My current query looks like this:

SELECT p.*, c.* FROM Products AS p LEFT JOIN Colors AS c ON c.ColorProductID=p.ProductID GROUP BY p.ProductID ORDER BY p.ProductID ASC, c.ColorID ASC LIMIT 0,20

The problem with this query is that it indeed fetches only 20 products from the database, but it also fetches only one available color per product, instead of all the available colors per product.

How can I alter my query to fetch only 20 products, but having no limit on the colors per product?

Thanks in advance!

Answer 1

SELECT p.*, c.* FROM (select * from ProductsLIMIT 0,20) AS p 
                LEFT JOIN Colors AS c ON c.ColorProductID=p.ProductID 
        ORDER BY p.ProductID ASC, c.ColorID ASC 

Edit: Fixed query