CODEWITHSUNDEEP
mysqlsql
Imposter
Imposter

Reputation: 31

LEFT JOIN some values are returned as NULL

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

Upvotes: 0

Views: 55

Answers (1)

DanB
DanB

Reputation: 2124

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;

Upvotes: 2

Related Questions