SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Algarve/Portugal
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select one image per record

    Hi all,
    I have two tables. One stores the rental objects (rental) and the other table stores the rental images (rental_image). When I now select a rental object I just want one image (the primary image) per object. The relevant image should have the lowest Position and as a second argument the lowest ID.

    Table rental:
    ID (Primary Key)
    Position
    Reference

    Table rental_image:
    ID (Primary Key)
    Rental_ID (Foreign Key)
    Position
    Image

    My current SQL command goes like this:
    Code MySQL:
    SELECT rental.ID, rental.Ref, rental_image.Image FROM rental, rental_image WHERE rental.ID=rental_image.Rental_ID group by rental.id order BY rental_image.pos, rental_image.id, rental.Ref

    The result works some how, but not always like expected.
    Can someone clear up my code?

    Thanks
    SelamatJalan
    "Prediction is very difficult,
    especially if it's about the future." Mark Twain

  2. #2
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Algarve/Portugal
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe it's unclear what I want.

    First select statement:
    Code MySQL:
    SELECT rental.ID, rental.Ref FROM rental
    Second statement:
    Code MySQL:
    SELECT rental_image.Image FROM rental_image where rental_image.ID = rental.ID order by rental_image.ID limit 1

    To explain it a bit further. I have many images per rental object, but I just want to select the first one, which has the lowest position, and which acts as the main image.

    Otherwise I have to do Image-select-statement in a loop, when I pick up the rental objects. And that's what I want to avoid.

    Thanks
    SelamatJalan
    "Prediction is very difficult,
    especially if it's about the future." Mark Twain

  3. #3
    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)
    no, it wasn't unclear, it's just moderately difficult sql
    Code:
    SELECT rental.ID
         , rental.Ref 
         , rental_image.Image 
      FROM rental
    LEFT OUTER
      JOIN ( SELECT Rental_ID
                  , MIN(ID) AS lowest_id
               FROM ( SELECT rental_image.Rental_ID
                           , rental_image.ID
                        FROM ( SELECT Rental_ID
                                    , MIN(Position) AS lowest_pos
                                 FROM rental_image
                               GROUP
                                   BY Rental_ID ) AS m1
                      INNER
                        JOIN rental_image
                          ON rental_image.Rental_ID = m1.Rental_ID
                         AND rental_image.Position = m1.lowest_pos
                    ) AS m2
             GROUP
                 BY Rental_ID ) AS m3
        ON m3.Rental_ID = rental.ID
    LEFT OUTER
      JOIN rental_image
        ON rental_image.Rental_ID = m3.Rental_ID
       AND rental_image.ID = m3.lowest_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Algarve/Portugal
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy, thanks for your support. It's working like charm, but I need to slightly extend the query and I don't have a glue how to get this managed.

    The table rental has a column called active, which indicates whether a rental object is available to the public or not; so I need to extend the query by the condition.
    Code MySQL:
    AND rental.Active='1'
    But I'm not sure where to put this peace of code to!

    And the second extension; a table holding the descriptions of each rental object in all available languages called rental_desc where I need the column Claim. This table has the Foreign Key Rental_ID to make the relation to the rental object. To me on a former query it just was a join, like:
    Code MySQL:
    AND rental.ID=rental_desc.Rental_ID AND Lng='1'
    But after so much derivatives I'm not sure where to put this code also.

    Thanks and Merry Christmas to you and your family and a good New Year
    SelamatJalan
    "Prediction is very difficult,
    especially if it's about the future." Mark Twain

  5. #5
    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 rental.ID
         , rental.Ref 
         , rental_desc.Claim
         , rental_image.Image 
      FROM rental
    INNER
      JOIN rental_desc
        ON rental_desc.Rental_ID = rental.ID
       AND rental_desc.Lng = 1
    LEFT OUTER
      JOIN ( SELECT Rental_ID
                  , MIN(ID) AS lowest_id
               FROM ( SELECT rental_image.Rental_ID
                           , rental_image.ID
                        FROM ( SELECT Rental_ID
                                    , MIN(Position) AS lowest_pos
                                 FROM rental_image
                               GROUP
                                   BY Rental_ID ) AS m1
                      INNER
                        JOIN rental_image
                          ON rental_image.Rental_ID = m1.Rental_ID
                         AND rental_image.Position = m1.lowest_pos
                    ) AS m2
             GROUP
                 BY Rental_ID ) AS m3
        ON m3.Rental_ID = rental.ID
    LEFT OUTER
      JOIN rental_image
        ON rental_image.Rental_ID = m3.Rental_ID
       AND rental_image.ID = m3.lowest_id
     WHERE rental.Active = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •