Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Retrieve ONLY First Picture Record For Each Customer

    Hello all:

    I need a specific SQL solution to use for an image gallery that would display the first picture for each customer for that specific job type. Here is some condensed sample data for each of the two tables.

    Reference TABLE
    jobNo refName refProj
    1 Johnson Concrete
    2 Blake Concrete
    3 Jones Landscaping

    ReferencePicture TABLE
    picID picDescrip refPicLink (which is a FK linked to jobNo)
    1 Sidewalk 1
    2 Driveway 1
    3 Wall 1
    4 Basement 2
    5 Wall 2
    6 Bushes 3

    So on my Concrete page, I would ONLY be retrieving the records with picID 1 and 4 and for the Landscaping page I would be retrieving picID 6.

    I have tried several combination of using DISTINCT and also TOP with my SELECT statements but I'm guessing there is a much more efficient way to handle this.

    Please let me know if I am being clear enough.

    Thanks for looking.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Something like this:
    Code:
    SELECT rp.*, r.refName, r.refProj
    FROM ReferencePicture rp
      JOIN Reference r ON r.jobNo = rp.refPicLink
    WHERE rp.picID = (SELECT min(rp2.picID) 
                      FROM ReferencePicture rp2
                      WHERE rp2.refPicLink = rp.refPicPicLink)

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Yup. That did the trick. Thanks much!!

Posting Permissions

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