MySQL select that takes 2 values from a column and returns a single row

Question

Hi, please could someone help me with this. I can't seems to get my mind around the SQL statement I need to get what I want. Below is my table.

| product_id  | product_name |  product_thumb_image  | 
------------------------------------------------------
|     1       |   ProductA   |  images/img_A.jpg    |
|     2       |   ProductB   |  images/img_B.jpg    |
|     3       |   ProductB   |  images/img_B2.jpg   |
|     4       |   ProductC   |  images/img_C.jpg    |
|     5       |   ProductD   |  images/img_D.jpg    |

Notice there are 2 ProductBs.

I would like a SQL statement to select the "product_thumb_image" such that it returns a row for each product name, but where a product has a 2nd image then a second column is also return.

e.g.

| mages/img_A.jpg  |  NULL/0/''          |
| mages/img_B.jpg  |  images/img_B2.jpg  |
| mages/img_C.jpg  |  NULL/0/''          |
| mages/img_D.jpg  |  NULL/0/''          |

Any help will be appreciated, thanks.

Answer 1

You're going to need two subselects. One to get the first image, one for the second:

SELECT t1.product_name, t2.product_thumb_image, t3.product_thumb_image
FROM mytable t1
LEFT OUTER JOIN (SELECT x.product_thumb_image
                 FROM mytable x
                 WHERE x.product_name = t1.product_name
                 LIMIT 0,1) t2
LEFT OUTER JOIN (SELECT x.product_thumb_image
                 FROM mytable x
                 WHERE x.product_name = t1.product_name
                 LIMIT 1,1) t3
GROUP BY t1.product_name

Answer 2

This is significantly easier to solve with ranking functions but MySQL does not support them yet. Instead, you can simulate a rank:

Select P.product_name
    , Min( Case When P.ProductRank = 0 Then product_thumb_image End ) As Image0
    , Min( Case When P.ProductRank = 1 Then product_thumb_image End ) As Image1
From    (
        Select product_id, product_name, product_thumb_image
            , (Select Count(*)
                From Products As P2
                Where P2.product_name = P1.product_name
                    And P2.product_id < P1.product_Id) As ProductRank
        From Products As P1
        ) As P
Group By P.product_name

Here's another way to write the same query which might perform better:

Select P.product_name
    , Min( Case When P.ProductRank = 0 Then product_thumb_image End ) As Image0
    , Min( Case When P.ProductRank = 1 Then product_thumb_image End ) As Image1
From    (
        Select P1.product_id, P1.product_name, P1.product_thumb_image
            , Count(P2.product_id) As ProductRank
        From Products As P1
            Left Join Products As P2
                On P2.product_name = P1.product_name
                    And P2.product_id < P1.product_Id
        Group By P1.product_id, P1.product_name, P1.product_thumb_image
        ) As P
Group By P.product_name

Answer 3

With a variable number of images per product, writing MySQL to return the columns you want is going to be difficult and rather clunky. Instead, why not run a simple GROUP_CONCAT to return a comma separated list, then split the results in your application. As you are returning a list of filenames, the comma separator is unlikely to occur in the filename:

SELECT
  `product_name`,
  GROUP_CONCAT(`product_thumb_image`) AS product_thumb_images
FROM `product`
GROUP BY `product_name`;

+--------------+------------------------------------+
| product_name | product_thumb_images               |
+--------------+------------------------------------+
| ProductA     | images/img_A.jpg                   |
| ProductB     | images/img_B.jpg,images/img_B2.jpg |
| ProductC     | images/img_C.jpg                   |
| ProductD     | images/img_C.jpg                   |
+--------------+------------------------------------+