CODEWITHSUNDEEP
mysql
Valor_
Valor_

Reputation: 3601

Select rows with same column values (Mysql)

I got following table structure.

 id  ||  column a  ||  column b 
---------------------------------
 1         1              2      
 2         5              1
 3         1              2
 4         2              3
 5         6              52
 6         1              1
 7         3              5
 8         6              52
 9         52             6
10         13             88

How to select rows with same values in both columns? So in my case i should get rows where id equals 1,3,5,8

Upvotes: 2

Views: 2270

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use grouping:

SELECT COLUMN_A, COLUMN_B
FROM mytable
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*) > 1

This will select all COLUMN_A, COLUMN_B duplicate pairs. To get the whole row you can use:

SELECT t1.ID, t1.COLUMN_A, t1.COLUMN_B
FROM mytable AS t1
INNER JOIN (
   SELECT COLUMN_A, COLUMN_B
   FROM mytable
   GROUP BY COLUMN_A, COLUMN_B
   HAVING COUNT(*) > 1
) AS t2 ON t1.COLUMN_A = t2.COLUMN_A AND t1.COLUMN_B = t2.COLUMN_B

Alternatively you can use EXISTS:

SELECT *
FROM mytable AS t1
WHERE EXISTS (
   SELECT 1
   FROM mytable AS t2
   WHERE t1.ID <> t2.ID AND 
         t1.COLUMN_A = t2.COLUMN_A AND 
         t1.COLUMN_B = t2.COLUMN_B)

Upvotes: 2

Eray Balkanli
Eray Balkanli

Reputation: 7990

Can you please try:

Select t1.id, t1.ColumnA, t1.ColumnB
From Table t1 
inner join Table t2 on (t1.id <> i2.id AND t1.columnA = t2.columnA AND t1.columnB = t2.columnB)

Upvotes: 2

Related Questions