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 nee开发者_JAVA百科d to add a Limit 0,1 on images table. How I can do it?
Thanks in advance.
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
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
Take a look at DISTINCT
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'
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.
精彩评论