CODEWITHSUNDEEP
mysqljoin
Andres SK
Andres SK

Reputation: 10974

MySQL: Using JOIN to get parent row with the most recent child row only

I have 3 tables:

I need to get the most recent news, each one with their category and only the first picture for that specific post, using o as the order field.

This is my current sql query:

SELECT
tn.date_news AS date_news,
tn.title AS title,
tn.text AS text,
tn.url AS url,
tc.name AS cat,
tp.file AS file
FROM news AS tn
JOIN news_cat AS tc ON tc.id_cat = tn.id_cat
JOIN (
    SELECT file FROM news_pic WHERE news_pic.id_news = tn.id_news ORDER BY temp.o LIMIT 1
) AS tp ON tp.id_news = tn.id_news
WHERE
tn.flg_featured = 1
ORDER BY tc.date_news DESC LIMIT 6

I'm getting this error message:

Column not found: 1054 Unknown column 'tn.id_news' in 'where clause'

This is the sqlfiddle with the tables' structure and some example data. Thanks for any suggestions.

Upvotes: 0

Views: 161

Answers (1)

Nick
Nick

Reputation: 147146

This is a greatest-n-per-group problem; you need to find the minimum value of o for each value of id_news and then JOIN news_pic to itself on the o value matching that minimum value to get the first picture. Note that you have a couple of other errors (tc.flg_featured should be tn.flg_featured and tc.date_news should be tn.date_news). This should give you the results you want:

SELECT
  tn.date_news AS date_news,
  tn.title AS title,
  tn.text AS text,
  tn.url AS url,
  tc.name AS cat,
  tp.file AS file
FROM news AS tn
JOIN news_cat AS tc ON tc.id_cat = tn.id_cat
JOIN news_pic tp ON tp.id_news = tn.id_news
JOIN (
  SELECT id_news, MIN(o) AS o
  FROM news_pic
  GROUP BY id_news
) AS tpm ON tpm.id_news = tn.id_news AND tpm.o = tp.o
WHERE tn.flg_featured = 1
ORDER BY tn.date_news DESC
LIMIT 6

Demo on SQLFiddle

Upvotes: 2

Related Questions