showing records where one field is duplicated and another is not

T

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
 
J

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top