SELECT rows having same value in two fields, and different value in another

1
votes

I want to select rows that have the same value in column1 and column2 but differ in column3.

Here's my table.

column1 column2 column3
a J abc
a K def
a L xyz
b J abc
b J def
b L xyz
c K def
c K def

Here's the output I want.

column1 column2
b J

Here's what I tried.

SELECT column1, column2
FROM my_table
GROUP BY column1, column2, column3
HAVING COUNT(column1) > 1;
2
mysqlsql

2 Answers

1
votes

I want to select rows that have the same value in column1 and column2 but differ in column3.

Use exists:

select t.*
from my_table t
where exists (select 1
              from my_table t2
              where t2.column1 = t.column1 and t2.column2 = t.column2 and
                    t2.column3 <> t.column3
             );

Your sample results, though, suggest that you just want column1/column2 pairs. If so:

SELECT column1, column2
FROM my_table
GROUP BY column1, column2
HAVING MIN(column3) <> MAX(column3);

Your version of the query just returns column1/column2 pairs that have a column3 value with more than one row.

0
votes

I think this should do it, small correction over your version:

SELECT column1, column2
FROM my_table
GROUP BY column1, column2
HAVING COUNT(DISTINCT column3) > 1;