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

Question

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.

Gordon Linoff · Accepted Answer · 2020-03-26 17:12:19Z

1

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
             );

answered Mar 26, 2020 at 17:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

user3931836 Over a year ago

Doesn't seem to work. It just says "t does not exist".... sorry, I'm a newbie at this...

Gordon Linoff Over a year ago

@user3931836 . . . That is your table name.

Gordon Linoff Over a year ago

@user3931836 . . . It is an alias in the subquery for your table name.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related