CODEWITHSUNDEEP
mysqlsql
Benson Kiprono
Benson Kiprono

Reputation: 129

Is there a way to find rows with same column in mysql

I have a table in MySQL called accounttransactions storing financial transactions. Now here there are two Columns, ref for reference numbers and adm for student's admission number respectively. I want to find in this table the number of students with the same transaction number but different students bearing in mind that a student can use the same transaction number more than once. I.E

---------------
| adm  | ref  |
--------------
| 102  | 2145 |
| 102  | 2145 |
| 103  | 2152 |
| 104  | 2152 |
---------------

for admission 102, the ref is okay. I want to find rows like admission 103 and 104.

I have tried using 

SELECT COUNT(ref) AS num, ref FROM accounttransactions GROUP BY ref HAVING num>1

but this gives me the count for the ref for all students even the ones with the same admission number

Upvotes: 1

Views: 82

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would use exists, but not with aggregation:

select a.*
from accounttransactions a 
where exists (select 1 
              from accounttransactions a2
              where a2.ref = a.ref and
                    a2.adm <> a.adm
             );

With an index on accounttransactions(ref, adm), this should have the best performance characteristics.

Upvotes: 1

Daxesh Radadiya
Daxesh Radadiya

Reputation: 73

I have used self join to find the output

SELECT e.* 
FROM accounttransactions e 
INNER JOIN accounttransactions f ON e.ref = f.ref 
WHERE e.adm != f.adm 
GROUP BY e.ref, e.adm

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

Use correlated subquery with exists

DEMO

select * from  accounttransactions a 
where exists 
   (select 1 from accounttransactions b where a.ref=b.ref having count(distinct adm)>1)

OUTPUT:

adm ref
103 2152
104 2152

Upvotes: 2

Related Questions