CODEWITHSUNDEEP
mysql
zerociudo
zerociudo

Reputation: 377

MySQL join two tables and limit and order both of them

There is a News table and a Comments table. I need to get 10 latest news posts from News table and Join them with 1 latest comment on that post.

The database structure and what I've tried so far is here:

CREATE TABLE News (newsId INT AUTO_INCREMENT, text VARCHAR(1000), date DATETIME, PRIMARY KEY(newsId));
CREATE TABLE Comments (commentId INT AUTO_INCREMENT, text VARCHAR(1000), date DATE, newsId INT, PRIMARY  KEY (commentId));

INSERT INTO News (newsId, text, date) VALUES
(NULL, "1st random text", NOW()), (NULL, "2nd random text", NOW()), (NULL, "3rd NEWS text", '2017-10-10 15:30:00'),
(NULL, "4th random text", '2017-10-10 15:35:00'), (NULL, "5th random text", '2017-10-10 15:32:00'),
(NULL, "6th random text", '2017-10-10 15:34:00'), (NULL, "7th random text", '2017-10-10 15:31:00');

INSERT INTO Comments (commentId, text, date, newsId) VALUES
(NULL, "1st random comment", NOW(), 1), (NULL, "2nd random comment", '2017-10-10 15:34:00' , 1), (NULL, "3rd NEWS comment", NOW(), 2),
(NULL, "4th random comment", '2017-10-10 15:39:00', 2), (NULL, "5th random comment", '2017-10-10 23:34:00',3),
(NULL, "6th random comment", NOW(),3), (NULL, "7th random comment", NOW(),4);

SELECT N.*, C.*
FROM News N((
ORDER BY N.date DESC
LIMIT 10)
    LEFT JOIN Comments C
    ON C.newsId = N.newsId
    ORDER BY C.date DESC
    LIMIT 1);

However this does not work and throws me a syntax error. Not sure how can i write this. Also I would prefer suggestions without sub-querying, thanks in advance.

EDIT. The error I am getting

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 

that corresponds to your MySQL server version for the right syntax to use near '((
ORDER BY N.date DESC
LIMIT 10)
    LEFT JOIN Comments C
    ON C.newsId = N.n' at line 2

Upvotes: 2

Views: 302

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

First you need get the latest 10 News

SELECT N.*
FROM News N
ORDER BY N.date DESC 
LIMIT 10

Then find the most recent comment. In my case I reject rows if exist a comment newer than it.

SQL DEMO

SELECT *
FROM (SELECT N.*
      FROM News N
      ORDER BY N.date DESC 
      LIMIT 10) as recent_news
LEFT JOIN  Comments C1
       ON  recent_news.newsId = C1.newsId
WHERE NOT EXISTS (SELECT 1
                  FROM Comments C2
                  WHERE C2.newsId = C1.newsId
                    AND C2.date > C1.date)

Upvotes: 1

sagi
sagi

Reputation: 40491

Does this solve your problem ? 

SELECT N.*, P.*
FROM News N
LEFT JOIN (SELECT c.* FROM Comments C
           LEFT JOIN Comments c1 ON(c.newsId = c1.newsId and c1.date > c.date)
           WHERE c1.date IS NULL) p
 ON (p.newsId = N.newsId)
ORDER BY N.date DESC
LIMIT 10;

You have more than 1 issue with your code. First , the first ORDER BY clause is wrong . Order by comes at the end of a query, you can't open parenthesis because the optimizer think you start a sub query .

Second, "I want the latest comment on each post" , that means you need the latest per group . Many RDBMS offer Window Function like row_number() to solve this. Unfortunately this is MySQL, which means you either have to make a self join(look at my inner query) or use some kind of correlated query .

Upvotes: 2

Related Questions