INNER JOIN: limit 0.1 in the second table

Question

I have 2 tables, one called “products” and one “images”. Images of each product are stored in the “images” of the table, so I can have 5 images for each product.

I want to make a choice that extracts only 1 image for each product. I am new to associations, so I don’t know how to solve this.

I'm trying to:

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

I need to add Limit 0.1 to the image table. How can i do this?

Thanks in advance.

Answer 1

It is better to avoid subqueries because they are slow in mysql.

- , , , :

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

( - ), groupwise max

Answer 2

, .

- :

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

Answer 3

DISTINCT

Answer 4

.

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

Answer 5

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

.

, 2 .

SELECT product data

Loop through product data {
    SELECT image data LIMIT 1
}

, / .