MySQL Forums
Forum List  »  Newbie

sql query with 3 tables
Posted by: pan ac
Date: August 07, 2004 02:58AM

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?

Thanks.
pac






Options: ReplyQuote


Subject
Written By
Posted
sql query with 3 tables
August 07, 2004 02:58AM
August 07, 2004 05:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.