I have a MySQL table like this:
id | user_id | title | slug ------------------------------ 1 | 1 | hello | hello 2 | 1 | hello | hello-1 3 | 2 | bye | bye 5 | 3 | bye | bye-1 I want to find duplicated entries. Entries with the same user_id, title and the slug which is ends with "-1".
These are duplicates:
1 | 1 | hello | hello 2 | 1 | hello | hello-1 And these are not, because the user_id's are different:
3 | 2 | bye | bye 5 | 3 | bye | bye-1 How can I find these duplicates?
One simple method concatenates the slugs together:
select user_id, title, group_concat(distinct slug) from t group by user_id, title; This puts them all on one row. Alternatively, you can use exists:
select t.* from t where exists (select 1 from t t2 where t2.user_id = t.user_id and t2.title = t.title and t2.slug <> t.slug ); You cound use having count(*) > a join to the related title
select * from my_table m inner join ( select title from my_table group by title having count(*) >1 ) t on t.title = m.title Looks to me that accepted answer just assume that slugs are different, NOT that they may be different just for the "-1" part, that's what the OP was looking for... as to say:
1 | 1 | hello | hello 2 | 1 | hello | other results in duplicate rows, but they are not. So it works for the values used as exemplification by the OP but not with others...
SELECT * FROM yourTable A1 WHERE EXISTS ( SELECT 1 FROM yourTable A2 WHERE A1.title = A2.title AND A1.user_id=A2.user_id AND (A1.slug=A2.slug OR A1.slug = CONCAT(A2.slug, "-1")) LIMIT 1, 1 ) The query above trys to solve the "-1" issue.
