Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select records for duplicates using multiple columns

Tags:

sql

mysql

I would like the select records from a table, or insert them into a new blank table where multiple of the columns is the same as another record in the database. The problem is similar to this Question. Find duplicate records in MySQL However that only compares one column. Also, one of my columns, lets say column C in the example below, is an integer. Like the question in the link above, I want each of the rows to be returned. Unforunately I am just not familiar enough with how joins work to figure this out on my own yet. I know that the code below doesn't resemble the actual SQL code need at all, it is just the clearest way I can think to describe the comparisons I am trying to get.

SELECT ColumnE, ColumnA, ColumnB, ColumnC from table where (   Row1.ColumnA = Row2.ColumnA &&   Row1.ColumnB = Row2.ColumnB &&   Row1.ColumnC = Row2.ColumnC ) 

Any help would be appreciated, all of the "select duplicates from MYSQL" questions I have seen use only one column as a comparison.

like image 466
keepitreall89 Avatar asked May 01 '13 18:05

keepitreall89


People also ask

How do I check if multiple columns have the same value in SQL?

In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared.


2 Answers

If you want to count duplicates among multiple columns, use group by:

select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates from table group by ColumnA, ColumnB, ColumnC 

If you only want the values that are duplicated, then the count is bigger than 1. You get this using the having clause:

select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates from table group by ColumnA, ColumnB, ColumnC having NumDuplicates > 1 

If you actually want all the duplicate rows returns, then join the last query back to the original data:

select t.* from table t join      (select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates       from table       group by ColumnA, ColumnB, ColumnC       having NumDuplicates > 1      ) tsum      on t.ColumnA = tsum.ColumnA and t.ColumnB = tsum.ColumnB and t.ColumnC = tsum.ColumnC 

This will work, assuming none of the column values are NULL. If so, then try:

     on (t.ColumnA = tsum.ColumnA or t.ColumnA is null and tsum.ColumnA is null) and         (t.ColumnB = tsum.ColumnB or t.ColumnB is null and tsum.ColumnB is null) and         (t.ColumnC = tsum.ColumnC or t.ColumnC is null and tsum.ColumnC is null) 

EDIT:

If you have NULL values, you can also use the NULL-safe operator:

     on t.ColumnA <=> tsum.ColumnA and         t.ColumnB <=> tsum.ColumnB and         t.ColumnC <=> tsum.ColumnC  
like image 66
Gordon Linoff Avatar answered Sep 24 '22 04:09

Gordon Linoff


why don't you try using union or creating temporary table. but personally, i do recommend using union than that of creating temporary table cause it would take you a longer time doing that. try doing this:

  select field1, field2 from(    select '' as field2, field1, count(field1) as cnt FROM list GROUP BY field2 HAVING cnt > 1     union     select ''as field1, field2, cound(field2) as cnt from list group by field1 having cnt > 1   ) 

hope this make sense.:)

like image 30
mCube Avatar answered Sep 25 '22 04:09

mCube