Jump to content


Photo

DISTINCT Help with SQL Query , Please help me.


  • Please log in to reply
8 replies to this topic

#1 rostros

rostros
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 02 August 2006 - 08:54 PM

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, 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]

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 August 2006 - 09:35 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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 rostros

rostros
  • Members
  • PipPip
  • Member
  • 10 posts

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 .


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 August 2006 - 11:36 PM

Yes, but then you'd get a random filename!
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 rostros

rostros
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 August 2006 - 08:16 AM

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


Ok this is the table structure so far

phpbb_attachments
attach_id
post_id

phpbb_attachments_desc
attach_id
physical_filename

phpbb_posts
post_id
topic_id

phpbb_topics
topic_id
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 ?







#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 August 2006 - 02:21 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 rostros

rostros
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 August 2006 - 02:26 PM

Any way round it ?

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 August 2006 - 02:30 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 rostros

rostros
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 August 2006 - 02:35 PM

Ok , thanks for your feedback.

Ill have a play around.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users