mardi 14 juin 2016

Select according to position and additional data

I have three tables that contain image data for a product. This is exactly the data I have to work with. Note that the thumbnail image for this product ID 10 is in the thumbnail table and only further identified by its file name.

  1. images: has image file names.

    id | product_id |    file
    -----------------------------
    1  |     10     | image1.jpg
    2  |     10     | image2.jpg
    3  |     10     | image3.jpg
    
  2. positions: has positions of the images (id is same as images).

    id | position
    --------------
    1  |    1
    2  |    2
    3  |    3
    
  3. thumbnail: has thumbnail (one per product).

    product_id |   file
    -----------------------
        10     | image3.jpg
    

I'm looking to write the most optimum query that will get me the files in the position ASC order, but with the thumbnail image always first regardless of its position value. So, I'm looking to get..

image3.jpg
image1.jpg
image2.jpg

I'm stuck trying to write this into a single query (if possible). I can run two queries an do a UNION and get what I need. Any better idea?

Aucun commentaire:

Enregistrer un commentaire