musicliftsme musicliftsme - 1 year ago 71
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

is in the
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 (
    is same as

    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
s in the
order, but with the
image always first regardless of its position value. So, I'm looking to get..


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

Answer Source

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 =
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.