Select MySQL rows where column contains same data in more than one record?

Use MySQL JOIN to select MySQL rows where column contains same data in more than one record. Let us first create a table −

mysql> create table DemoTable
(
   UserId int,
   UserName varchar(20)
);
Query OK, 0 rows affected (0.54 sec)

Insert records in the table using insert command −

mysql> insert into DemoTable values(10,'John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(11,'Sam');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values(12,'Larry');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(13,'David');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values(14,'Larry');
Query OK, 1 row affected (0.10 sec)

Display records from the table using select command −

mysql> select *from DemoTable;

This will produce the following output −

+--------+----------+
| UserId | UserName |
+--------+----------+
| 10     | John     |
| 11     | Sam      |
| 12     | Larry    |
| 13     | David    |
| 14     | Larry    |
+--------+----------+
5 rows in set (0.00 sec)

Following is the query to select rows where a column contains same data in more than one record −

mysql> SELECT DISTINCT tbl1.*
FROM DemoTable tbl1
JOIN DemoTable tbl2
on tbl2.UserId <> tbl1.UserId
AND tbl2.UserName=tbl1.UserName;

This will produce the following output −

+--------+----------+
| UserId | UserName |
+--------+----------+
| 14     | Larry    |
| 12     | Larry    |
+--------+----------+
2 rows in set (0.14 sec)

karthikeya Boyini

I love programming (: That's all I know

Updated on: 30-Jul-2019

353 Views

Kickstart Your Career

Get certified by completing the course

Get Started