3

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.

Community
  • 1
  • 1
Smiter
  • 1,069
  • 1
  • 14
  • 33
  • 1
    `select *` is never good. What columns do you really need being returned? – juergen d Apr 07 '14 at 22:13
  • baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted I have updated the question to account for this – Smiter Apr 07 '14 at 22:16
  • 3
    Does `select distinct baseFeeds.siteURL, feedFinishes.timeTaken, feedFinishes.timeSubmitted from ...` work for you with the first query? And change the `left join` to an `inner join` – juergen d Apr 07 '14 at 22:17
  • Which DBMS are you using? Postgres? Oracle? –  Apr 07 '14 at 22:19
  • Still returns 18 rows. The same 6 being repeated. – Smiter Apr 07 '14 at 22:19

1 Answers1

4

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
potashin
  • 44,205
  • 11
  • 83
  • 107