Complex Query Question

[CPGDeveloper]

Hello -- Any help with this would be greatly appreciated:

I have data something like this:

ID, RESULT
1, Y
1, Y
1, Y
2, N
2, N
3, Y
3, N
4, Y
4, N

I only want to pull records where the ID has both a result of 'Y' and a result of 'N' (In this case, that would pull all records with ID 3 and 4) How would I do that in one query?

 

[Legacy 456155]

I'm betting there is a more elegant way to do this. Nevertheless, this will work for your specific question.

SELECT *
FROM MyTable
WHERE ID In (SELECT ID FROM MyTable WHERE Result = "Y") And ID In (SELECT ID FROM MyTable WHERE Result = "N")

 

[petertenthije]

Instead of one complicated formula, you could try two simple formulas to get the desired result:

QUERY1

SELECT DISTINCT Table1.ID, Table1.Result
FROM Table1;

QUERY2

SELECT Query1.ID, Count(Query1.ID) AS CountOfID
FROM Query1
GROUP BY Query1.ID
HAVING (((Count(Query1.ID))=2));