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

Published on 30-Apr-2019 06:45:33

• Related Questions & Answers
• MySQL Select where value exists more than once
• Use JOIN to select record with more than one condition using AND?
• Select rows having more than 2 decimal places in MySQL?
• MySQL search if more than one string contains special characters?
• Delete more than one rows from a table using id in MySQL?
• MySQL query to find all rows where string contains less than four characters?
• MySQL Select Rows where two columns do not have the same value?
• How to sort more than one column at a time in MySQL?
• SELECT where row value contains string in MySQL?
• MySQL query to select rows where column value is only 0, group by another column?
• Ways to place items in n^2 positions such that no row/column contains more than one in C++
• SELECT * WHERE var == [one of many alternatives] in MySQL?
• MySQL: selecting rows where a column is null?
• Can we GROUP BY one column and select all data in MySQL?
• MySQL query to select rows older than a week?