CODEWITHSUNDEEP
mysqlsqljoinrelational-database
dulange
dulange

Reputation: 291

MySQL Join left row with only the first right row

Say we have a simple table of blog articles, for example:

aid  title  content
1    Foo    Lorem Ips…
2    Bar    Dolor Sit…
3    Boo    Amet Cons…

… and another table for the comments:

cid  aid  name    comment                date
1    1    zaphod  First!                 1404294939
2    1    arthur  Not you again!!111     1404296182 
3    1    marvin  It’s all useless anyw… 1404299811

And now I want to generate a list of all the articles with the most recent comment of the respective article. That means I have to left join the articles table on the comments table, but for each article row I only want the comment row with the highest date value or in other words: only the first row when I do ORDER BY date DESC LIMIT 1 for every article-comment connection.

What is the best (or maybe easiest) way to do this?

Upvotes: 0

Views: 127

Answers (4)

Lijo
Lijo

Reputation: 6778

select title,contents,name,coment from articles join comment
using(aid) where date =(select max(date) from comment group by aid);

Upvotes: 0

Charvee Shah
Charvee Shah

Reputation: 720

You can do this using joins only.It is much faster than Sub query.

 select articles.*,comments1.* from 
    Articles articles
    LEFT JOIN (Comments comments1
    LEFT JOIN Comments comments2 on comments1.date<comments2.date 
and comments1.aid=comments2.aid
    ) on comments2.cid IS NULL and comments1.aid=articles.aid;

Example of SQLFiddle : http://sqlfiddle.com/#!2/51a5bb/4

Upvotes: 3

GarethD
GarethD

Reputation: 69759

The methodology needed is to first find the latest comment date per article:

SELECT  aid, MAX(Date) AS Date
FROM    Comments
GROUP BY aid;

You can then place this logic inside a subquery and join back to comments using aid and date to get all the fields:

SELECT  c.*
FROM    Comments AS c
        INNER JOIN
        (   SELECT  aid, MAX(Date) AS Date
            FROM    Comments
            GROUP BY aid
        ) AS mc
            ON mc.aid = c.aid
            AND mc.Date = c.Date;

Then you can add a join to your Articles table. If all articles have comments, or you only want articles with comments then you can use:

SELECT  *
FROM    Articles AS a
        INNER JOIN Comments AS c
            ON c.Aid = a.aid
        INNER JOIN
        (   SELECT  aid, MAX(Date) AS Date
            FROM    Comments
            GROUP BY aid
        ) AS mc
            ON mc.aid = c.aid
            AND mc.Date = c.Date;

If not you will need to use a LEFT JOIN with parentheses to ensure the INNER JOIN on the subquery does not remove articles with no comments:

SELECT  *
FROM    Articles AS a
        LEFT JOIN (Comments AS c
            INNER JOIN
            (   SELECT  aid, MAX(Date) AS Date
                FROM    Comments
                GROUP BY aid
            ) AS mc
                ON mc.aid = c.aid
                AND mc.Date = c.Date)
            ON c.Aid = a.aid;

Example on SQL Fiddle

This is really just shorthand for:

SELECT  *
FROM    Articles AS a
        LEFT JOIN 
        (   SELECT  c.*
            FROM    Comments AS c
                    INNER JOIN
                    (   SELECT  aid, MAX(Date) AS Date
                        FROM    Comments
                        GROUP BY aid
                    ) AS mc
                        ON mc.aid = c.aid
                        AND mc.Date = c.Date
        ) AS c
            ON c.Aid = a.aid;

But since MySQL materialises all subqueries, this avoids the unnessesary materialisation of Comments. Comparing the execution plans of the two queries shows that the former will perform better.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Find the last comment date per article with a group by statement. Use this to join article and last comment.

select a.*, c.*
from articles a
join
(
  select aid, max(date) as date
  from comments
  group by aid
) last_comment on (last_comment.aid = a.aid)
join comments c on (c.aid = last_comment.aid and c.date = last_comment.date);

Upvotes: 0

Related Questions