sql query help

Anon

i'm writing a simple message board. i have 2 tables:

topics_tbl -- topic_id
posts_tbl -- post_id / topic_id / date
(stripped down)

so for each record in topics_tbl, there will be several for it in posts_tbl, referencing it with the topic_id key.

i want a query that will return a row for each topic, with the most recent date from posts_tbl ... if you get what i mean. i came up with this ...

SELECT topics_tbl., posts_tbl.
FROM topics_tbl
LEFT JOIN posts_tbl USING (topic_id)
GROUP BY topics_tbl.topic_id ORDER BY posts_tbl.date DESC

... but it doesn't show the most recent date from posts_tbl. i can't think where the solution lies ... the join is occuring at the first match it finds 🙁

tomhath

I think the GROUP BY is messing it up. Also you can LIMIT it to the first row. The "date" column is a date and not a char, right?

SELECT topics_tbl., posts_tbl.
FROM topics_tbl
LEFT JOIN posts_tbl
ON topics_tbl.topic_id = posts_tbl.topic_id
ORDER BY posts_tbl.date DESC LIMIT 0,1

Anon

The date column is a datetime column yeah. I don't want to LIMIT the results - I want 1 row returned for every row that exists in topics_tbl, with 1 row from posts_tbl matching the topic_id being selected by its most recent date.

The only solution I can think of is to have an extra field in topics_tbl that holds the last post date, and I update that whenever a post is made ... this is not good database design though 🙁

any ideas?

thanks, andy.

tomhath

I think I finally understand. Does your database have the EXISTS keyword?

SELECT t., p.
FROM topics_tbl t
LEFT JOIN posts_tbl p USING (topic_id)
where not exists (
select * from posts_tbl p2
where t.topic_id = p2.topic_id
and p2.date > p.date)

tomhath

The manual explains how to get the functionality of a NOT EXISTS by using a left join and checking for NULL

Anon

I dunno if mySQL has that keyword or not, if so then I don't think it exists in my version (3.22.32) ...

ERROR 1064: You have an error in your SQL syntax near 'EXISTS (SELECT * FROM posts_tbl p2
WHERE t.topic_id = p2.topic' at line 4

tomhath

Should be something like this to replace the EXISTS. I didn't test it, mysql manual has examples.

SELECT t., p.
FROM topics_tbl t
LEFT JOIN posts_tbl p USING (topic_id)

left join posts_tbl p2
on t.topic_id = p2.topic_id
and p2.date > p.date
where p2.post_id is null