SQL - How to join top ordered results from pictures table with product table ?

Question

I have a products table, and a pictures table as such:

Products

• id
• name
• model

Pictures

• id
• pid
• url
• order

The picture for each pid with the highest order is the cover picture. There can be more than 1 picture per product.

I want to join this result when I query the products, so it would show these result

id, name, model, url (cover picture/highest order for this pid)
---------------------------------------------------------------

but I'm going nuts trying to get it to work. So far I've manage to get it to work by joining the picture as below, but I can't get it to join only the picture with the top order...

SELECT p.id,
       p.name,
       p.model,
       x.url 
  FROM products p 
  JOIN pictures x ON (p.id = x.pid)

Can anyone help me out please?

Answer 1

Use:

SELECT p.id,
       p.name,
       p.model,
       x.url 
  FROM PRODUCTS p 
  JOIN PICTURES x ON x.pid = p.id
  JOIN (SELECT t.pid,
               MAX(t.order) AS max_order
          FROM PICTURES t
      GROUP BY t.pid) y ON y.pid = x.pid
                       AND y.max_order = x.order