SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! - you're a genius. I would never have worked that out in a million years. Thank you so much.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •