views:

22

answers:

1

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?

+1  A: 

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
OMG Ponies
wow thanks for the quick response! That worked like a charm, though I don't fully understand the 2nd join/subquery yet, I will study it. Thanks a bunch man!
Winterain
If 2 records have the same maximum order then there will be duplicates.
Dave Barker
Dave you're right... I need to find a fix...
Winterain
adding GROUP BY p.id at the end seems to fix the duplicates.
Winterain