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
精彩评论