selecting rows that are the same in one column, but different in another

Question

X    Y    DATE
1    20   20120101
1    21   20120101
2    30   20120201
3    40   20120201
3    41   20120301

I want to select any rows that have another row where X is the same, but the date is different, i.e. the answer would be

3    40   20120201
3    41   20120301

Answer 1

Try this...

SELECT *
FROM YourTable
WHERE X IN (
  SELECT T1.X
  FROM YourTable T1 INNER JOIN
       YourTable T2 ON T1.X = T2.X
  WHERE T1.DATE <> T2.DATE
);

This should work in most ANSI-compliant database products.

Answer 2

select distinct t1.*
  from table t1
  join table t2
    on (t1.X = t2.X and t1.date <> t2.date);