LEFT JOIN but only return one record - LEFT JOIN with LIMIT

Databases
1

Hi,

I have a table of products and I also have a table of images. Each product can have multiple (unlimited) images assigned to it. When I view the product details all the possible images are displayed - however when I view a list/grid of products I only want one image to display for each product.

My product table is:
tblproducts

the relevant fields are:
prodId (UNIQUE ID)
prodTitle

My Images table is:
tblimages

the relevant fields are:
imgId (UNIQUE ID)
imgprodId (ID of the product that this image is assigned to)
imgfilename (VARCHAR of the image filename)
imgsort (INT - the sort order of the image - lowest value should be the default image)

I’d like to produce a recordset of products and each product shows the “default” image for that product (i.e. the image with the lowest value in the imgsort field)

My SELECT statement that doesn’t work is:

“SELECT prodId, prodTitle FROM tblproducts LEFT JOIN tblimages ON prodId = imgprodId LIMIT 1 ORDER BY imgsort ASC WHERE prodEnabled = ‘Y’ ORDER BY prodTitle ASC”

I know the “LIMIT 1 ORDER BY imgsort ASC” bit doesn’t work but I thought it might explain what I want to acheive - i.e. for each product there is just one image (from all the possible images stored in tblimages for that product) and it’s the image with the lowest imgsort value.

I hope someone can help.

Many thanks.

2 
SELECT prod.prodId
     , prod.prodTitle
     , imgs.imgfilename 
  FROM tblproducts AS prod
LEFT OUTER
  JOIN ( SELECT imgprodId
              , MIN(imgsort) AS min_imgsort
           FROM tblimages
         GROUP
             BY imgprodId ) AS subq
    ON subq.imgprodId = prod.prodId
LEFT OUTER
  JOIN tblimages AS imgs
    ON imgs.imgprodId = subq.imgprodId
   AND imgs.imgsort = subq.min_imgsort
 WHERE prod.prodEnabled = 'Y' 
ORDER 
    BY prod.prodTitle ASC
3

Wow! - you’re a genius. I would never have worked that out in a million years. Thank you so much.