MySQL JOIN, GROUP BY, ORDER BY

Question

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.

Answer 1

SQLFiddle demo

select products.id,
       coalesce(t1.mid,t2.mid) as image_id      

from products
left join (select min(id) mid,product_id 
                  from images where `default`=1
                  group by product_id ) t1
        on products.id=t1.product_id
left join (select min(id) mid,product_id 
                  from images where `default`=0
                  group by product_id ) t2
        on products.id=t2.product_id

Answer 2

Looks like I was just missing a 'DESC' in the subquery's ORDER BY

:\