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

Answer 2

Take a look at DISTINCT

Answer 3

SELECT * FROM products 
LEFT JOIN images ON products.id=images.prod_id
WHERE products.id='1' LIMIT 1

This will return the first image found for your product and all the product details.

If you want to retieve multiple products then I would suggest doing 2 queries.

SELECT product data

Loop through product data {
    SELECT image data LIMIT 1
}

Doing complex single queries can quite often end up being more expensive than a couple/few smaller queries.

Answer 4

The key here is correlated subqueries.

select
  *
from
  products p,
  (
  select
    *
  from
    images i
  where
    i.prod_id = p.id
  limit 1
  ) as correlated
where
  p.cat = 'shoes'

Answer 5

It's best to avoid subqueries because they are slow in mysql.

If you want to get any image associated to product, you can do it in fast but not very nice way:

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

If you want to get a first image( by any criteria ), apply groupwise max techniques