Finding and displaying duplicates

Question

I have a table. I'd like to search within this table for duplicate titles.
Lets say my data is as follows:

title1, title2, title3, title1, title4, title2, title9

I'd like for my query to search this table and output only the duplicates. So the output would look like:
title1
title1
title2
title2

I know how I can find the duplicates, but it only shows me one of the duplicates and not both.

SELECT id, title, artist_id FROM sm019_songs GROUP BY title HAVING count(title) > 1

Any ideas?

Answer 1

Here's one way to do it with a subquery. It might make sense to load the subquery into a temp table first. (This would work in SQL server, not sure what the exact MySql syntax would be.)

select id, title, artist_id
from sm019_songs
where title in
(
    SELECT title
    FROM sm019_songs
    GROUP BY title
    HAVING count(title) > 1
)

Answer 2

I'd just join the table to itself:

SELECT S1.*, S2.*
FROM songs S1, songs S2
WHERE S1.title = S2.title
  AND S1.id != S2.id

-----N