CODEWITHSUNDEEP
mysqlduplicates
user3286692
user3286692

Reputation: 383

Select all duplicate rows based on one or two columns?

I Have a table named contacts with fields

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+

I want to display all duplicates based on first_name and (/ or) last_name, e.g:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | mukta      | chourishi |
|  2 | mukta      | chourishi |
|  3 | mukta      | john      |
|  4 | carl       | thomas    |
+----+------------+-----------+

If searched on just first_name it should return: 

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

But if searched on both first_name and last_name should return:

+----+
| id |
+----+
|  1 |
|  2 |
+----+

Upvotes: 6

Views: 16522

Answers (2)

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58291

One way to achieve your result is using nested query and having clause: In inner query select those having count more then one, and in outer query select id:

Check following example for single column selection criteria:

Create table:

CREATE TABLE `person` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `first` varchar(120) NOT NULL,
    `last` varchar(120) NOT NULL
);

Insert tuple:

INSERT INTO `person` ( `first`, `last`) VALUES
("mukta", "chourishi"),
("mukta", "chourishi"),
("mukta", "john"),
("carl", "thomas" );

The result you need:

mysql> SELECT  `id` 
    -> FROM `person` 
    -> WHERE `first`=(SELECT `first` FROM `person` HAVING COUNT(`first`) > 1);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

[ANSWER]

But as if you selection criteria is on the basis of more than one columns then you can make use of JOIN.

To explain it I am writing a selection query that creates an intermediate table that will be use in JOIN as second operand table.

Query is select all fist name and column those duplicates with some of other rows:
For example select rows in which first and last name repeats

mysql> SELECT `first`, `last`,  count(*)  as rows 
    -> FROM `person` 
    -> GROUP BY `first`, `last` 
    -> HAVING count(rows) > 1;
+-------+-----------+------+
| first | last      | rows |
+-------+-----------+------+
| mukta | chourishi |    2 |
+-------+-----------+------+
1 row in set (0.00 sec)

So you have only one pair of first and last names those repeats (or is duplicates with some other rows).

Now, question is: how to select id of this row? Use Join! as follows: 

mysql> SELECT  p1.`id`
    -> FROM `person` as p1
    -> INNER JOIN (
    ->     SELECT `first`, `last`,  count(*)  as rows
    ->     FROM `person` 
    ->     GROUP BY `first`, `last` 
    ->     HAVING count(rows) > 1) as p
    -> WHERE p.`first` = p1.`first` and p.`last` = p1.`last`;  
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.06 sec)

you can select on the basis of as many columns as you wants e.g. single column if you want using join then remove last name.

Upvotes: 10

Saddam Abu Ghaida
Saddam Abu Ghaida

Reputation: 6749

and you write sql function which takes two parameters firstname and lastname and inside the functions you write your conditions if lastname=null find duplicates for firstname, and if firstname is null, find duplicates for the lastname, and so on so forth

the statemnets inside the conditions is 

-- to show the duplicates for firstname
select id from table where first_name='name' 

-- to show duplicates for firstname and last name
select id from table where first_name='name' and last_name='lname' 

-- to show duplicates for firstname or last name
select id from table where first_name='name' or last_name='lname'

Upvotes: -2

Related Questions