I have a table of products:
CREATE TABLE products (`id` INT);
And a table of images for those products:
CREATE TABLE images (`id` INT, `product_id` INT, `default` TINYINT(1));
I need to select all the products, and join the images table so that images with (default = 1) will be preferred, and if a product has no images with (default = 1), an image with (default = 0) will be shown in its place.
Here's an image showing what I'm looking for:
Right now I have this query:
SELECT p.id, i.id
FROM products AS p
LEFT JOIN (
SELECT product_id, url
FROM images
ORDER BY default
) AS i
ON p.id = i.product_id
GROUP BY p.id
ORDER BY p.name
Which doesn't prioritize "default" images. The subquery doesn't seem to do anything.
解决方案
Looks like I was just missing a 'DESC' in the subquery's ORDER BY
:\