Search code examples
mysqlselect

selecting only one row for each product in the table in mySQL database

I have a bit complex mysql select query question.

I have 2 tables as shown in the image below. One is for products and the other one is the images of products. I can select the products with images with inner join.

But, since there are a lot of items in the "product images table", I want to select just one image from the table for each product.

Here are my 2 main tables : enter image description here

and here is the desired result table : enter image description here

It doesn't matter which image is retrieved from the images table but to make it less complex I can -for example- get the min(imageID) one.

I want to use this query to display products in my e-commerce page. I don't want to get all the images on page is loaded. So, just one image for each product is ok for me.

Thanks for any help.

Solution

  • You could actually implement this as a join with exists logic:

    SELECT pImageID, productID, productImage
FROM productImages p1
WHERE NOT EXISTS (
    SELECT 1
    FROM productImages p2
    WHERE p2.productID = p1.productID AND
          p2.pImageID < p1.pImageID
);

    In plain English, this says to select every product image record having the smallest pImageID for each group of productID records.