CODEWITHSUNDEEP
mysqlsql
Umut Çağdaş Coşkun
Umut Çağdaş Coşkun

Reputation: 1472

How to find duplicated entries which has different slug?

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?

Upvotes: 0

Views: 185

Answers (3)

holden
holden

Reputation: 1779

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.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269993

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
             );

Upvotes: 2

Related Questions