开发者

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

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?


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜