my sql query - duplicate records

[pac]

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
pac

[johnphy]

the way is to retrieve only the right last 30 id's you want with a "group by" clause.
When you have the id's, use your sql as a nested query and perform the joins to retreive all the needed fields.
understand? nested queries are the answer!

email-me if sql needed.

[pac]

hi Johnphy,

I tried many times, I think i am rather week in the join operations, have tried inner join but couldn't get it right, would appreciate you shed some light on it, perhaps an axmaple of the sql queries would be very much appreciated.

Thanks
pac

[pac]

I tired this but still not what i would like it to be :

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)

Help anyone ?

[JasonMichael]

Is this an established program you're trying to modify?

I'm trying to figure out a fix for your problem, but realizing that the design of the database is not exactly the way I would do it.... especially with how you have p.forum_id and p.author_id - what's the point to author_id if you already have p.forum_id pointing at a user account?

I would use field names like user_id.... I would say your database is ready for a revamp - sooner the better. Flatten out the layout of some of your tables and take out some redundency. That way, when you have a requirement, like the one you're mentioning, you won't have the mess and hassle, later on.

I just pray its not too late for you to change it... I'd really like to help you out with this though.