DISTINCT Help with SQL Query , Please help me.

[rostros]

Okay, i am modding a phpbb database that has an attachment mod, i have had to connect 4 tables together which is the easy part, i have tried to use DISTINCT to remove duplicate post_id's but it doesn't work due to the number of tables im adding.

here is my SQL Query

SELECT phpbb_attachments_desc.attach_id, phpbb_attachments_desc.physical_filename, phpbb_attachments.attach_id, [color=red]phpbb_attachments.post_id[/color], 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]

[fenway]

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.

[rostros]

[quote author=fenway link=topic=102773.msg408430#msg408430 date=1154554541]
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.
[/quote]


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 .

[fenway]

Yes, but then you'd get a random filename!

[rostros]

[quote author=fenway link=topic=102773.msg408502#msg408502 date=1154561794]
Yes, but then you'd get a random filename!
[/quote]

Ok this is the table structure so far

[b]phpbb_attachments [/b]
[color=green]attach_id[/color]
[color=teal]post_id [/color]

[b]phpbb_attachments_desc[/b]
[color=green]attach_id[/color]
physical_filename

[b]phpbb_posts[/b]
[color=teal]post_id[/color]
[color=blue]topic_id[/color]

[b]phpbb_topics[/b]
[color=blue]topic_id[/color]
topic_filetime
topic_attachment


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 ?

[fenway]

I know what you want to do... but if you group by post_id, and there are 6 different filenames, which one are you going to get?  Once you GROUP BY, all non-grouped columns become meaningless.

[rostros]

Any way round it ?

[fenway]

Not exactly... you'd have to forgoe the group by.  You can either get a bunch of post_ids from a separate query, or use a join and put a limit there.  Ultimately, there's nothing to work around, per se -- once you group, you get an aggregate result.

[rostros]

Ok , thanks for your feedback.

Ill have a play around.