CODEWITHSUNDEEP
mysqlsql
Smiter
Smiter

Reputation: 1197

How to use DISTINCT in WHERE CLAUSE to avoid repeated row returns

The result of this SQL query should return a set of 6 rows.

SELECT baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted 
FROM feedFinishes 
LEFT OUTER JOIN baseFeeds 
ON feedFinishes.GUID = baseFeeds.GUID 
WHERE feedFinishes.nowDate = baseFeeds.nowDate 
AND baseFeeds.siteURL LIKE '%www.example.com%'

Currently it returns 18 rows, the correct 6 rows are being repeated 3 times. I figure a solution to this is to have another WHERE clause something similar to:

WHERE DISTINCT feedFinishes.ID

After researching this I have tried:

SELECT baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted 
FROM feedFinishes 
JOIN baseFeeds 
ON feedFinishes.GUID = baseFeeds.GUID 
WHERE baseFeeds.siteURL LIKE '%www.example.com%' AND feedFinishes.ID in
     (SELECT ID FROM feedFinishes GROUP BY ID HAVING COUNT(ID)=1);

After the discussion found here

Alas, this still returns 18 rows. I believe the answer is similar to here

Any advice would be appreciated.

Upvotes: 3

Views: 35369

Answers (1)

potashin
potashin

Reputation: 44581

You should apply group by clause.

SELECT 
baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted
FROM feedFinishes 
JOIN baseFeeds 
ON feedFinishes.GUID = baseFeeds.GUID 
WHERE baseFeeds.siteURL LIKE '%www.example.com%' 
GROUP BY
baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted

Upvotes: 4

Related Questions