INNER JOIN: limit 0,1 on the second table

Question

I have 2 tables, one called "products" and one "images". The table "images" hold the images of each products, so I can have 5 image per product.

I want to make a select that retrive only 1 image for each product. I'm new to joins so i dont know how to solve this.

I'm trying with:

    SELECT * 
      FROM products
INNER JOIN images ON products.id=images.prod_id 
     WHERE products.cat='shoes'

I need to add a Limit 0,1 on images table. How I can do it?

Thanks in advance.

Answer 1

Maybe a subselect is a better solution here.

Something like this:

SELECT
productId,
productName,
(SELECT imageData FROM Image i WHERE i.productId = productId LIMIT 1) AS imageData
FROM Products