Select rows with same column values (Mysql)

Question

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

I think you need to be more specific about how you decide which rows appear in the resuilt of this query! — RiggsFolly
– RiggsFolly, Commented Feb 29, 2016 at 18:09

Eray Balkanli · Accepted Answer · 2016-02-29 18:12:34Z

2

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)

edited Feb 29, 2016 at 18:12

answered Feb 29, 2016 at 17:58

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Valor_ Over a year ago

you are near... currently i got duplicated rows... example: id,columnA,columnB,id,columnA,columnB.... And results are also duplicated, but correct one

Eray Balkanli Over a year ago

Try Select t1.id, t1.columnA, t1.columnB instead using *. If you have duplicate records try using DISTINCT in select. (Answer updateD)

Giorgos Betsos · Accepted Answer · 2016-02-29 18:19:18Z

2

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)

edited Feb 29, 2016 at 18:19

answered Feb 29, 2016 at 18:14

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

Select rows with same column values (Mysql)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related