CODEWITHSUNDEEP
mysql
luispcosta
luispcosta

Reputation: 572

Find entry in table with a column with same value

The title is a bit wrong, i can explain better here. Say i have a MySQL table like this:

mysql> select * from people;

+------+---------------+
| name | number        |
+------+---------------+
| John |        100000 |
| Alex |        200000 |
| Tim  |        500000 |
| Alice|        100000 |
| Peter|        500000 |
+------+---------------+

And now i want a query which returns the names of the people who have the same number, ie, for this table i would a query which would return John and Alice (they have the same number, 100000) and Tim and Peter (the same number 500000)

Upvotes: 0

Views: 27

Answers (2)

SMA
SMA

Reputation: 37083

Try self join as below:

SELECT p1.name
FROM people p1 INNER JOIN people p2
ON p1.number = p2.number
WHERE p1.name != p2.name

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this by aggregating on the number column and using group_concat(). A having clause selects only cases where there is more than one name:

select number, group_concat(name) as names
from table t
group by number
having count(*) > 1;

Upvotes: 1

Related Questions