Question 339648 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    One opption is to use ROW_NUMBER() analytic function like here:

    --  S a m p l e    D a t a :
Create Table products (PRODUCT_ID Int, PRODUCT_NAME Varchar(32));
Insert Into products (PRODUCT_ID, PRODUCT_NAME) VALUES
(1, 'Sample product 1'), (2, 'Sample product 2'), (3, 'Sample product 3'),
(4, 'Sample product 4'), (5, 'Sample product 5'), (6, 'Sample product 6'),
(7, 'Sample product 7'), (8, 'Sample product 8'), (9, 'Sample product 9');

Create Table productimages (IMAGE_ID Int, PRODUCT_ID Int, PRODUCT_IMAGE Varchar(32));
Insert Into productimages (IMAGE_ID, PRODUCT_ID, PRODUCT_IMAGE) VALUES
(1, 1, 'images/img1.jpg'), (2, 1, 'images/img2.jpg'), (3, 1, 'images/img3.jpg'),
(4, 1, 'images/img4.jpg'), (5, 2, 'images/img5.jpg'), (6, 2, 'images/img6.jpg'),
(7, 2, 'images/img7.jpg'), (8, 3, 'images/img8.jpg'), (9, 3, 'images/img9.jpg');

    --      S Q L :
Select   i.IMAGE_ID, i.PRODUCT_ID, i.PRODUCT_IMAGE
From  ( Select      i.IMAGE_ID, i.PRODUCT_ID, i.PRODUCT_IMAGE, 
                    ROW_NUMBER() Over(Partition By i.PRODUCT_ID Order By i.PRODUCT_ID) "RN"
        From        productimages i
      ) i
Where  RN = 1;

    /*    R e s u l t : 
IMAGE_ID    PRODUCT_ID  PRODUCT_IMAGE
----------  ----------  -----------------
         1           1  images/img1.jpg
         5           2  images/img5.jpg
         8           3  images/img8.jpg    */
    Login or Signup to reply.
  3. 0

    One option is to get the earliest imageID for each product, then join this dataset with images and products tables to ensure only products with images are included :

    SELECT i.*
FROM products p
INNER JOIN productImages i ON p.productID = i.productID
INNER JOIN (
  SELECT productID, MIN(pImageID) AS MIN_pImageID
  FROM productImages i
  GROUP BY productID
) AS t ON t.productID = i.productID AND t.MIN_pImageID = i.pImageID
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search