showing records where one field is duplicated and another is not

[tdp]

How would I write a query to do the following?

Field X Field Y
1 A
1 B
2 C
2 C
3 D

I want the resulting query to only show the two records where Field X is 1
(i.e. I only want to show results where there are at least two records that
have Field X duplicated AND Field Y different. I DON'T want to show either
records where both fields X and Y are the same or where there's only one
record in Field X).

Thanks very much!
tdp

 

[John W. Vinson]

How would I write a query to do the following?

Field X Field Y
1 A
1 B
2 C
2 C
3 D

I want the resulting query to only show the two records where Field X is 1
(i.e. I only want to show results where there are at least two records that
have Field X duplicated AND Field Y different. I DON'T want to show either
records where both fields X and Y are the same or where there's only one
record in Field X).

Thanks very much!
tdp

SELECT A.FieldX, A.FieldY, B.FieldY
FROM table AS A
INNER JOIN table AS B
ON A.FieldX = B.FieldX
WHERE A.FieldY < B.FieldY;

 

[tdp]

John--Thank you very much. I could only get it to work by changing your code
from "<" to "<>", but you definitely pointed me in the right direction.

Much appreciated.
tdp