- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- MySQL Select where value exists more than once
- Select rows having more than 2 decimal places in MySQL?
- Use JOIN to select record with more than one condition using AND?
- MySQL Select Rows where two columns do not have the same value?
- MySQL search if more than one string contains special characters?\n
- MySQL query to find all rows where string contains less than four characters?
- Delete more than one rows from a table using id 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?
- How to sort more than one column at a time in MySQL?
- Can we GROUP BY one column and select all data in MySQL?
- MySQL: selecting rows where a column is null?
- MySQL - SELECT … WHERE id IN (..) order with particular column?
- How do I select data from one table only where column values from that table match the column values of another table in MySQL?
- MySQL query to select rows older than a week?

Advertisements