CODEWITHSUNDEEP
mysqlsqlselectprimary-keydistinct
user2793987
user2793987

Reputation: 199

Simple MySQL Duplicate Removal

I'm attempting to just get rows from my database. However, for some reason, some rows return as duplicates even though there is a DISTINCT command on the IDPosts portion. The problem is the AND/OR statement for checking for post expiration date. Any ideas would be appreciated to get this down to zero duplicates. Please let me know if you need more information.

SELECT 
      DISTINCT IDPosts, 
      profile_picture,
      body, 
      post_date, 
      expires, 
      filename, 
      username 
FROM Posts, Users 
WHERE Posts.IDUser = Users.IDUser AND expires IS NULL OR expires >= NOW() 
ORDER BY `Posts`.`post_date` DESC

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270693

I don't know if this is related to your problem, but you should probably have parentheses around the conditions on expires in the where clause. The logic is probably not doing what you intend.

SELECT DISTINCT IDPosts, profile_picture, body, post_date, expires,
      filename, username
FROM Posts JOIN
     Users 
     ON Posts.IDUser = Users.IDUser
WHERE 
      (expires IS NULL OR expires >= NOW() )
ORDER BY `Posts`.`post_date` DESC;

Now that I also fixed the join syntax to use proper explicit joins.

Upvotes: 2

Related Questions