musicliftsme musicliftsme - 5 months ago 10
SQL Question

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
file
s 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?

Answer

If I am interpreting the use of position correctly, I would imagine something like this should work.

SELECT i.file
FROM images AS i 
INNER JOIN positions AS p ON i.id = p.id
LEFT JOIN thumbnail AS t ON i.product_id = t.product_id AND i.file = t.file
WHERE i.product_id = ?
ORDER BY (t.product_id IS NULL), p.position
;

(t.product_id IS NULL) is basically an "is not thumbnail" check; since false orders earlier, the image that is not "not the thumbnail" will be first.

Comments