选择具有相同列a但不同列b的值

Question

I am using phpmyadmin database.

I have a table with columns 'admitno' and 'name' . I want to select values with same Admit No but different name and display those admitno along with all names against it.

I have tried code like

SELECT Admitno, Name
FROM table1 t1
WHERE EXISTS
(
SELECT 1 FROM table1 t2
WHERE t2.Admitno= t1.Admitno
AND t2.Name<> t1.Name
) 

But not working in my case. Any suggestion please

Answer 1

You have to use self join and then filter data on the conditions that the two alias of table have same Admitno but different name

SELECT * from table1 a join table1 b where a.Admitno = b.Admitno and a.Name!=b.Name