CODEWITHSUNDEEP
sqljoingroup-bysql-order-by
NewInTheBusiness
NewInTheBusiness

Reputation: 1475

Get latest date in multiple join

I'm struggling with this one and even though there are some somewhat similar questions on Stack Overflow I can't find the right solution. I have three tables: categories, topics and posts where I want to join them and show each category in categories together with the subject of the latest topic from topics that had something posted in it and finally the latest date that post was posted from the posts table.

I successfully get everything to display like I want except that I can't get the latest post record from each category.

Here's the query:

SELECT c.cat_id, c.cat_name, c.cat_description, t.topic_subject, t.topic_id, p.post_date FROM categories c 
LEFT JOIN topics t ON c.cat_id = t.topic_cat 
LEFT JOIN posts p ON p.post_topic = t.topic_id AND p.post_date = ( SELECT MAX(post_date) as post_date FROM posts WHERE post_topic = t.topic_id ) 
GROUP BY c.cat_id ORDER BY UNIX_TIMESTAMP(post_date) DESC

I realize it should have something to do with that the topic_id in the WHERE clause is not the one I'm looking for. ANother one like this: 

SELECT c.cat_id, c.cat_name, c.cat_description, t.topic_subject, t.topic_id, p.post_date FROM categories c 
LEFT JOIN topics t ON c.cat_id = t.topic_cat AND t.topic_id = ( SELECT post_topic FROM posts ORDER BY UNIX_TIMESTAMP(post_date) DESC LIMIT 1 ) 
LEFT JOIN posts p ON p.post_topic = t.topic_id AND p.post_date = ( SELECT MAX(post_date) as post_date FROM posts WHERE post_topic = t.topic_id ) 
GROUP BY c.cat_id ORDER BY UNIX_TIMESTAMP(post_date) DESC

will obviously only show the right post for the category with the latest post overall in it. How to get the latest post for all of the categories?

Upvotes: 1

Views: 215

Answers (2)

valex
valex

Reputation: 24144

SELECT c.cat_id, c.cat_name, c.cat_description, tp.topic_subject, tp.topic_id, tp.maxPostDate
FROM categories c 

LEFT JOIN (select t.topic_cat,t.topic_subject,t.topic_id, max(post_date) maxPostDate  from topics t
           LEFT JOIN posts p ON p.post_topic = t.topic_id
           group by t.topic_cat,t.topic_subject,t.topic_id) tp
on (c.cat_id=tp.topic_cat)


where tp.maxPostDate = (select max(post_date) from topics t2
                       LEFT JOIN posts p2 ON p2.post_topic = t2.topic_id
                       where t2.topic_cat=c.cat_id
                       )


ORDER BY UNIX_TIMESTAMP(tp.maxPostDate) DESC

Note: This query can output for example 2 rows for one category if it has 2 different topics with the same maximum post date.

Upvotes: 1

Daniel Hermosel
Daniel Hermosel

Reputation: 423

First: in your Select queries you must use some agregate functions for the fields that you don't include in the GROUP BY clause.

Now. For gettin the post with max timestamp for each category, you have to make a two steps query. In first step you will get the max timestamp for category and in the second all the other fields. Yes, we'll use cat_id + timestamp as primary key. It could provide some duplicate records for the same cat_id, but it will be very extrange that in real time ocours.

The code will be similar to:

SELECT c.cat_id, MAX( p.post_date ) as max_date
into #tmp_table
FROM categories c 
    LEFT JOIN topics t ON c.cat_id = t.topic_cat 
    LEFT JOIN posts p ON p.post_topic = t.topic_id 
GROUP BY c.cat_id 

SELECT * -- what you'll need
from #tmp_table as tmp
    LEFT JOIN categories as c on c.cat_id = tmp.cad_id
    LEFT JOIN topics t ON c.cat_id = t.topic_cat 
    LEFT JOIN posts p ON p.post_topic = t.topic_id AND p.post_date = tmp.max_date
-- order by -- what you'll need

It's a solution, better one could be nice.

Upvotes: 0

Related Questions