Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: sql to get top 4 from 1 table and corresponding image from other table

    I have a DB with an item table and an images table. (each item has unlimited images)
    I need to get the 4 most expensive items from table1 and display the first related image from each one.

    I have tried using ASP to loop thru but I am having trouble creating 4 variables which I can use further down the web page. image1, image2 etc.

    Plus I am sure it should all be done with one SQL statement creating a temp recordset from which I can make my variables?

    Any ideas what the SQL would be?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It can be done in one SQL statement, but first you have to decide how you define the first related image per item.

    To get the top 4 most expensive items
    Code:
    SELECT TOP 4 price
    FROM   items
    ORDER
        BY price DESC
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks,
    so far so good, I'm with you so far !

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So I repeat:
    Quote Originally Posted by georgev
    [how do you] define the first related image per item
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    there are two image IDs, one relating to the ITEM and the other ID is auto-generated.
    If ITEM has an ID of 12 all of its images will be imageID 12, but the images all have an autoID which I can use to determin the first image posted.
    Last edited by darkmunk; 06-29-08 at 04:09.

Posting Permissions

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