Skip to content
Advertisement

PHP/SQL Find duplicate rows in a column, with the condition that another column is different

I need an SQL query to do the following task:

I have two columns. Column ‘foo’ and column ‘bar’.

The query needs to return results if and only if column ‘foo’ has different values but ‘bar’ has the same values.

For example:

Foo               Bar
---------------------
1                 John
1                 Lee
2                 James
3                 Robin    <- the value '3' needs to be returned
3                 Sally
1                 Peter
1                 John
4                 Brian
2                 Robin    <- the value '2' needs to be returned

If I was to run the query on the above dataset, then both rows indicated withe arrows above would be returned, because ‘bar’ is the same on both rows, but ‘foo’ is different.

Any help would be appreciated.

Thank you.

Advertisement

Answer

You can do what you want using exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.bar = t.bar and t2.foo <> t.foo
             );
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement