Select Unique ID with only a set of values

[GTXY20]

Hello all;

If I have the following data:

ID Value
1 A
1 B
2 A
2 B
2 C
3 A
3 B

How would I go about only selecting the ID's that have only have the combination of values A & B - so my select query woud return:

ID Value
1 A
1 B
3 A
3 B

ID 2 is not returned because it also has a value of C.

I have tried:

SELECT table.ID, table.Value,
FROM table
WHERE (((table.Value) ="A")) AND (((table.value) ="B"))

but no luck.

Thanks in advance!

[nico5038]

Multiple row processing isn't easy by queries.
In this case however we could use a GroupBy query to get the needed results.
The needed "algoritm" is the fact that you're looking for ID's having two hits when selecting "A" and "B" and when selecting all records.
The easy way would be to make separate queries:
1) Select ID, Count(ID) as Counter from tblX where IDValue IN ("A","B") GroupBy ID Having Count(ID) = 2;
this will give the set of ID's with atleast "A" and "B", but we need to exclude those also having a "C"
2) Select ID, Count(ID) as Counter from tblX GroupBy ID Having Count(ID) = 2;
This query however will also return "A" and "C" as "valid".
3) JOIN the two above queries by ID to get the needed set of ID's and use that to display all data when needed.

Getting the idea ?

Nic;o)

[GTXY20]

Thanks so much - I get it - this seems to work.

When my queries are finalized I will post.

[nico5038]

Keep me posted !

Nic;o)