DISTINCT Help with SQL Query , Please help me.
Posted 02 August 2006 - 08:54 PM
here is my SQL Query
SELECT phpbb_attachments_desc.attach_id, phpbb_attachments_desc.physical_filename, phpbb_attachments.attach_id, phpbb_attachments.post_id, phpbb_posts.post_id, phpbb_posts.topic_id, phpbb_topics.topic_id, phpbb_topics.forum_id, phpbb_topics.topic_time, phpbb_topics.topic_attachment
FROM phpbb_attachments_desc, phpbb_attachments, phpbb_posts, phpbb_topics
WHERE phpbb_attachments_desc.attach_id = phpbb_attachments.attach_id AND phpbb_attachments.post_id = phpbb_posts.post_id AND phpbb_posts.topic_id = phpbb_topics.topic_id AND phpbb_topics.topic_attachment = 1
ORDER BY phpbb_topics.topic_time DESC
What I Need is to remove these duplicate post_id's from the this query, as i want to show a preview of the attachment posted in the topic, please see attachment for screenshot.
Any help would be great. thanks
[attachment deleted by admin]
Posted 02 August 2006 - 09:35 PM
Posted 02 August 2006 - 09:42 PM
You're right, you can't use DISTINCT for just one of N columns... however, if you use a GROUP BY (the proper way), you'll make any other column values from that table meaningless. Please explain.
How the board works at the moment is a user can post mutiple posts to one topic, im trying to create a overview of posted topics, including the filename taken from the very first post of each topic (e.g a preview of whats been posted in that topic), hence thats why DISTINCT would of been great to remove all the duplicates post_ids .
Posted 03 August 2006 - 08:16 AM
Yes, but then you'd get a random filename!
Ok this is the table structure so far
I Wish to Display all the topics with an attachment, with a single post_id , filetime also DESC to show the latest. From the above SQL i have been able to do this but I have mutiple post_id's because sometimes there is more than a couple of post's per topic.
physical_filename topic_id post_id topic_attachment topic_filetime
dscf_0045.jpg 5666 34444 1 (timestamp)
Img0067.jpg 5667 35544 1 (timestamp)
IF DISTINCT wont work would it be possible to create a function at end of the SQL like phpbb_post_id > 1 or something to always increment it somehow ?
Posted 03 August 2006 - 02:21 PM
Posted 03 August 2006 - 02:30 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users