I have 3 tables and i want to display
the combination of some fields of the 3 tables into the result.

p table fields (Posts table for a forum):
author_id
topic_id
forum_id
post_date

t table fields (Topic Table for a forum):
title
views
posts

f table fields (main Forum table):
id
name

My SQl statement:

SELECT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM p, f, t
WHERE f.id = t.forum_id AND p.topic_id = t.tid
ORDER BY p.post_date DESC
Limit 0, 30

the results give the last 30 postings in descending order and displayed on the website.

Problem:
i only want unique topic_id to be displayed within the latest 30 posts, now if more than one person discuss the same topic within the 30 latest post, both posts will be listed.

p.author_id p.topic_id p.forum_id p.post_date t.title t.views
323 123 general 05/08/04 mysql 45
345 123 general 04/08/04 mysql 45
346 111 advanced 04/08/04 sybase 119

In the above table, i want the result to display only first and 3rd line.
How to resolve the above problem?

----

On this problem i tried the sql as follows:

SELECT p.author_id, p.topic_id, p.forum_id, p.post_date, p.icon_id, t.title, t.views, t.posts, f.name

FROM p, f, t

WHERE f.id = t.forum_id AND p.topic_id = t.tid

GROUP BY p.topic_id

ORDER BY p.post_date DESC

The results is almost there but instead of showing the lates posts
by members, it shows order posts.

The Group By sucessfully filtered out the duplicate post title,
the it seems to me the ORDER BY p.post_date DESC has not effect-
can't grab the latest post made by members,
the system does not return the latest entry, but rather seems randomly.

Many Thanks

Hi,

The following query should do what you want, but may be slow if you have many rows in your p table (p2 is another alias for p) :

SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM p, f, t
WHERE f.id = t.forum_id AND p.topic_id = t.tid
AND p.post_date=(select max(p2.post_date) from p2 where p2.topic_id=p.topic_id)
ORDER BY p.post_date DESC
Limit 0, 30

Tell me if it works as you want it to.

appreciate your help so much, but I got an error when doing the query :

SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM posts p, forums f, topics t
WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date = (
SELECT max( p2.post_date )
FROM posts p2
WHERE p2.topic_id = p.topic_id )
ORDER BY p.post_date DESC
LIMIT 0 , 30

MySQL said:
#1064 - You have an error in your SQL syntax near 'SELECT max( p2.post_date )
FROM posts p2
WHERE p2.topic_id = p.topi' at line 4

any idea what's wrong ?

and here's another query that does not produce what i want

SELECT
p.author_id,
p.topic_id,
p.forum_id,
p.post_date,
p.icon_id,
t.title,
t.views,
t.posts,
f.name
FROM
posts p,
forums f
INNER JOIN topics t ON t.last_post = p.post_date
WHERE f.id = t.forum_id
GROUP BY p.topic_id
ORDER BY p.post_date DESC";

Because of the Group by topic, it show all distinct lines but
not the latest being shown (seems like the order by post_date can't help here)

Any help is appreciated !!

Ok. Using MAX without a GROUP BY Clause works in Oracle, but seems not to work in MySQL (in Oracle there is nothing like 'LIMIT 0 , 30'). Who says "Normalized SQL ?"

By the way, in your case, you should in my opinion avoid a group by. This should work fine, I think :

SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM posts p, forums f, topics t
WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date >= ALL(
SELECT p2.post_date
FROM posts p2
WHERE p2.topic_id = p.topic_id )
ORDER BY p.post_date DESC
LIMIT 0 , 30

Thanks RBARAER! You certainly saved me!

I just noticed that MySQL 4.0 Stable does not support sub-query!
That's why i got the errors! and Only MySQL 4.1.3 beta support subquery!!
I installed the beta version and tried your suggestions, both worked but the results are very different! your second suggestion executed 100 times faster.
Guess the Max slow down the whole thing.

SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM posts p, forums f, topics t
WHERE f.id = t.forum_id AND p.topic_id = t.tid
AND p.post_date=(select max(p2.post_date) from posts p2 where p2.topic_id=p.topic_id)
ORDER BY p.post_date DESC
Limit 0, 30

took 152,281 msec

SELECT DISTINCT p.author_id, p.topic_id, p.forum_id, p.post_date, t.title, t.views, t.posts, f.name
FROM posts p, forums f, topics t
WHERE f.id = t.forum_id AND p.topic_id = t.tid AND p.post_date >= ALL(SELECT p2.post_date
FROM posts p2 WHERE p2.topic_id = p.topic_id ) ORDER BY p.post_date DESC LIMIT 0 , 30;

Took 1,875 msec !!

Thanks once again for the help! Really appreciate your advice!

Dex