Tuesday, March 27, 2012

duplicate results returned in query

I am getting duplicate results returned in this query if someone comments on a news item :( if i could limit results to 1 for just post_id it would be perfect but not sure how to integrete it into this query.
This is a query part of a php script I wrote to display the news posted in a thread... You can view the final result at http://overclocked.lancamp.com to see what im talking about.

SELECT t.topic_id, x.bbcode_uid, t.topic_title, x.post_text, t.topic_time, t.topic_views, t.topic_replies, t.topic_poster, u.username, p.enable_html, p.enable_bbcode, p.enable_smilies
FROM phpbb_topics t, phpbb_posts p, phpbb_posts_text x, phpbb_users u
WHERE t.forum_id =16
AND p.forum_id =16 AND p.topic_id = t.topic_id AND x.post_id = t.topic_first_post_id AND u.user_id = t.topic_poster
ORDER BY p.post_time DESC

Thanks for whoever helps!Hi,
This is not so easy to answer while I don't know your tables.
Normally you can avoid double results with DISTINCT in the SELECT clause.
advice: build a simple simpel test database and with a few tables and a few field in each table. Than try the same query.
If the fault still remains please send me the tables and the exact query.
Best regards
Robertsql

No comments:

Post a Comment