0

so I have this query:

SELECT 
 n.newsId newsId, n.date newsDate, n.text newsText, c.date commentDate, c.text 
 commentText 
FROM 
 (SELECT * FROM news ORDER BY date DESC LIMIT 10) n 
LEFT JOIN 
 (SELECT comments.* FROM comments, news WHERE comments.newsId = news.newsId ORDER BY comments.date DESC LIMIT 1) c 
ON 
 n.newsId=c.newsId

And the result is mostly ok, but there are some null values for some reason. Please have a look, thank you :)

results below

results

Imposter
  • 31
  • 9
  • 1
    Well that's what `LEFT JOIN` does, it fill the fields with `NULL` when there are no matches. Given your second nested `SELECT` will ever only return 1 record, this query will by design always return at least 9 rows filled with `NULL`, and all 10 of them when the latest comment doesn't belong in any of the 10 most recent news. – Havenard Oct 19 '18 at 19:13

1 Answers1

2

You want the newest comment for top 10 newest news?

You can do this with ranking function (if you have mysql 8), or with variables.

Rank function in MySQL

http://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/

Try this (untested) :

select newsId, newsDate, newsText, commentDate, commentText from (
    SELECT news.newsId, news.date newsDate, news.text newsText, comments.text commentText, comments.date commentDate, 
            @curRank := case when news.newsId = @curNewsId then @curRank + 1 else 1 end rnk,
            @curNewsId := news.newsId
    FROM news
    inner join comments on news.newsId = comments.newsId,
    (select @curNewsId := 0, @curRank := 0) tmp
    ORDER BY news.date DESC, comments.date desc
) sub 
where rnk = 1
order by newsDate desc
LIMIT 10;
DanB
  • 2,022
  • 1
  • 12
  • 24
  • does't work, shows bad results. Looks a bit complicated too lol – Imposter Oct 19 '18 at 19:12
  • shows only 5 result, but they are in top 10 that I need. – Imposter Oct 19 '18 at 19:16
  • I move limit 10 to the main query. Now? – DanB Oct 19 '18 at 19:20
  • now there are 10 results. I think that it works good too, I need to check. I don't really understand this new stuff, I need to read a bit about it :D – Imposter Oct 19 '18 at 19:24
  • This is not easy to read... If you have mysql 8, you should use new ranking function, which is really a better way to do this. Run only the subquery, you'll see rnk column that was used to filter only for first comment on the news. – DanB Oct 19 '18 at 19:28
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182165/discussion-between-raulis-radziukas-and-daniel-blais). – Imposter Oct 19 '18 at 19:39