Geek asks and answers

Select all duplicate rows based on one or two columns?

I have a table with a name contactswith fields

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

I want to display all duplicates based on first_nameand / or last_name, for example:

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

If you searched only first_name, it should return:

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

But if the search is on both first_nameand last_nameshould return:

+----+
| id |
+----+
|  1 |
|  2 |
+----+
+4
source share
2 answers

One way to achieve your result is to use a nested query and a having clause: in the internal query, select those that have more than one account, and in the external query, select id:

Check the 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" );

Result:

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]

, , JOIN.

, , , JOIN .

Query :
, , first last name

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)

, first last, ( ).

: id ? ! :

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)

, , , join, .

+5

sql-, firstname lastname , , lastname = null firstname, firstname null, .. /p >

statemnets

-- 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'
-1

More articles:


All Articles