CODEWITHSUNDEEP
mysqlleft-join
shershen
shershen

Reputation: 9993

MySQL left join with one ordered result from another table

I've got two tables: products and news - news are about products so news table has row pid (for product id), news have different dates so it can be ordered by date. And i need a query to show some data from products table joined with ONE and LATEST news title and date attached to it - i am trying to use query like this:

SELECT products.product_title, products.`product_key`,
news.`news_date`, news.`news_text` FROM products LEFT JOIN
news ON products.`product_key`= (SELECT news.`pid` FROM news
WHERE news.`pid` = products.`product_key`
ORDER BY news.`news_date` DESC LIMIT 1)
ORDER BY products.`product_title`

but it doesn't work properly, may be i should use GROUP BY operator instead of subquery?

Upvotes: 0

Views: 387

Answers (2)

shershen
shershen

Reputation: 9993

found it at last - the right thing is to get MAX row value from second table and than add it to WHERE-condition:

SELECT products.product_title, products.product_key, news.news_date,
news.news_text FROM products LEFT JOIN news ON products.product_key= news.pid
WHERE news.news_date = (SELECT MAX(news.news_date) FROM news WHERE news.pid = products.product_key) ORDER BY products.product_key

Upvotes: 1

John Woo
John Woo

Reputation: 263703

this is UNTESTED and willing to edit if it gives invalid result.

SELECT  a.product_key, a.product_title, 
        b.news_text, MAX(b.news_date)
FROM products a LEFT JOIN news b
        on a.product_key = b.pid
GROUP BY a.product_key, a.product_title, b.news_text

Upvotes: 1

Related Questions