mysql select where limit, join where

Question

I have following two MySQL tables

• products (product_id, name, category)
• products_sizes (product_id, size)

every product can have multiple sizes

I need to make a query that

1. selects a product based on category and size (if size is set) - e.g. category = 'shoes' and size = '10'
2. it must still return all sizes for the product, but only if the product has the specific size as on of its sizes
3. it must at the same time limit the results of products to a certain number with an offset

What I have now the query below. I works but doesn't constrain by category and only returns the one size specified (here '10') - I need all sizes, but only for the products that have '10' among its sizes...

SELECT products.*
     , products_sizes.*
FROM (
   SELECT *
   FROM products 
   LIMIT $limit OFFSET $offset
   ) AS products
LEFT JOIN products_sizes 
   ON products.products_id = products_sizes.products_id 
WHERE products_sizes.size = 10

... and if I add WHERE category = 'something' query returns nothing...

SELECT products.*
     , products_sizes.*
FROM (
   SELECT * 
   FROM products 
   WHERE category = 'shoes' 
   LIMIT $limit OFFSET $offset
   ) AS products
LEFT JOIN products_sizes 
   ON products.products_id = products_sizes.products_id
WHERE products_sizes.size = 10

??

UPDATE: getting closer...

After reading the answers I now have this:

SELECT *
FROM products AS p 
LEFT JOIN products_sizes AS s 
    ON p.product_id = s.product_id
    WHERE s.product_id 
        IN (SELECT product_id FROM s WHERE size = 10)
    AND p.category = 'shoes'

It satisfies my initial question's first two requirements:

1. Returns results constrained by category and size
2. Returns all sizes for each product if one of its sizes equals the specified size

... BUT I still need to limit the results to a certain number of products. If I put a LIMIT $limit at the very end of the query, it will limit the number of sizes returned and not the number of products... Thanks for the input.

Answer 1

The WHERE clause in your main query is defeating the purpose of your LEFT JOIN, making it the same as an INNER JOIN. It's very possible that there are not any rows selected by your derived table that match (shoes of size 10).

Unless there is something about your data not shown, you can try this:

SELECT products.*
     , products_sizes.*
FROM products 
JOIN products_sizes 
   ON products.products_id = products_sizes.products_id
WHERE products.category = 'shoes' 
   AND products_sizes.size = 10
LIMIT $limit OFFSET $offset