CODEWITHSUNDEEP
phpmysqlinner-join
Luciano
Luciano

Reputation: 1455

INNER JOIN: limit 0,1 on the second table

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.

Upvotes: 4

Views: 20361

Answers (5)

Naktibalda
Naktibalda

Reputation: 14110

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

Upvotes: 4

Lizard
Lizard

Reputation: 45002

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.

Upvotes: -1

Donnie
Donnie

Reputation: 46923

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'

Upvotes: 0

erenon
erenon

Reputation: 19118

Take a look at DISTINCT

Upvotes: 0

Sjoerd
Sjoerd

Reputation: 75609

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

Upvotes: 12

Related Questions