SQL: Statement to show 1 child record per parent in a One-to-Many relationship

Question

I have 2 tables, a product table and an image table. Products can have more than 1 image per record.

I need an SQL statement that will list 1 image per product rather than all images per product.

A scaled down example of my current effort is:

SELECT Product.RefCode, Img.ImgPath
FROM Product INNER JOIN (
    SELECT ProductImg.ImgPath, ProductImg.PrdFK
    FROM ProductImg 
    LIMIT 0,1) AS Img ON Product.PrdID = Img.PrdFK;

The above example returns 0 results, however, if i remove the LIMIT 0,1 from the Sub SELECT i get a complete list of all products and all images.

I just want 1 image record per product record to be returned.

---

I have revised "a programmer" code to MySQL and it works fine.

SELECT Product.RefCode, 
        (Select ProductImg.ImgPath
         From ProductImg
         Where Product.PrdID = Img.PrdFK LIMIT 0,1) AS ImgPath
FROM Products

For some reason when trying "Bill Karwin's" solution, it sent the server loading for a long time. Thank you both for the help.

Answer 1

This should work:

SELECT Product.RefCode,
       (Select Img.ImgPath
        From ProductImg
        Where Product.PrdID = Img.PrdFK LIMIT 0,1) As ImgPath
FROM Product

If you have a way to determine which image should the select return, then you can add a where and maybe an Order By in the sub-select.

In the following example, we'll get only the latest of the active images.

SELECT Product.RefCode,
       (Select Img.ImgPath
        From ProductImg
        Where Product.PrdID = Img.PrdFK
          And IsActive = 1
        Order By EntryDate Desc LIMIT 0,1) As ImgPath
FROM Product

Edited and corrected 'Top 1' to 'Limit 0,1'.

Answer 2

MySQL has some flexibility here. You can use GROUP BY to restrict the result to one row per product, and then MySQL will choose which image arbitrarily. In practice, it tends to be the image that is stored physically first in the database, but you don't have any control over that.

SELECT p.RefCode, i.ImgPath
FROM Product p INNER JOIN ProductImg i 
  ON (p.PrdID = i.PrdFK)
GROUP BY p.PrdID;

If you want 1 specific image per product, there are ways to do that too. You need to include that in your requirements. :-)

Answer 3

If you have an ID column on the Image table, this should work (no matter what the SQL product or version)--

   SELECT
            p.RefCode,
            i1.ImgPath
    FROM
            Product p
    INNER JOIN
            ProductImg i1 ON p.PrdId = i1.PrdFk
    LEFT JOIN
            ProductImg i2 ON i1.PrdId = i2.PrdId
            AND i1.ImgId < i2.ImgId
    WHERE
            i2.ImgId IS NULL

Answer 4

What you need is a flag on the Image table to specify if it is the main image to be displayed. There are ways of pulling an arbitrary image, but I think it would be better to make sure you pull the correct image.