IN-Predicate means OR - I need AND

[candide_sh]

Hello,

I have a table like this:

tblEACodes:
==============================
SK AQ_FKEY aqeEA_FKEY
== ======= ====
10 26 6
21 57 4
23 57 6
42 57 30

My SELECT clause:

SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE tblEACodes.aqeEA_FKEY In (4,6,30);

shows all records if only one of the WHERE criteria is TRUE.
What I need is a query showing me only SK if ALL criteria is met. So
the IN-predicate is wrong for me.

In this example the row with SK = 10 should not be displayed which
happens with the IN-clause shown above.
Any hints? Thank you,

 

[John Spencer]

So, can the combination of AQ_FKEY and AgeEA_FKEY ever be repeated in your
table. In other words, is the combination of those two fields always unique?

If so, you could use
SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE AQ_FKEY in
(SELECT AQ_FKEY
FROM tblEACodes as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY AQ_FKEY
HAVING Count = 3)

If the combination is not unique your query becomes a bit more complex in that
you have to create a query that returns the unique combinations and then do
the count on that

SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE AQ_FKEY in
(SELECT B.AQ_FKEY
FROM (
SELECT DISTINCT AQ_FKEY, aqeEA_FKEY
FROM tblEACodes
)as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY B.AQ_FKEY
HAVING Count = 3)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Jerry Whittle]

The SQL statement is doing exactly what you ask of it. You aren't asking
anything about the AQ_FKEY field which I'm assuming is important in that
there must be a 57 for each 4, 6, and 30 in the aqeEA_FKEY field.

Something like this may work. If the combination of the AQ_FKEY and
aqeEA_FKEY fields are not unique, it could return bogus records.

SELECT *
FROM TblEACodes
Where TblEACodes.AQ_FKEY IN
(SELECT TblEACodes.AQ_FKEY
FROM TblEACodes
WHERE TblEACodes.aqeEA_FKEY In (4,6,30)
GROUP BY TblEACodes.AQ_FKEY
HAVING Count(TblEACodes.aqeEA_FKEY)=3);

 

[Jerry Whittle]

Hi John,

I swear that I didn't read your post first. Great minds think alike!

I like how you took it one more step to deal with duplicates.

 

[Michel Walsh]

If you want the SK having all the specified aqeEA_FKEY, then put the
required aqeEA_FKEY in a table (a temporary one? ) without duplicated values
and use the technique exposed at

http://www.mvps.org/access/queries/qry0016.htm


It is essentially a simple inner join which acts like an intersection of two
sets.


Hoping it may help,
Vanderghast, Access MVP

 

[candide_sh]

Hello folks,

two remarks:
1. combination of AQ_FKEY and aqeEA_FKEY ist UNIQUE
2. HAVING Count = 3 may be working, but:
the number of rows may vary as you can see here:

AQ_FKEY = 26 has one row
AQ_FKEY = 57 has three rows

I read the stuff (link from Michel) but I don't understand if it would
work for me?

As the number of rows and also the IN-clause's values are always
different I have to support the number of criteria to fill the value
in the HAVING-clause by frontend-code. Ok, I give John's first version
a try

SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE AQ_FKEY in
(SELECT AQ_FKEY
FROM tblEACodes as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY AQ_FKEY
HAVING Count = 3)

Thank you for your help.

 

[John Spencer]

Small error in my SQL statement. Last line should read
HAVING Count(*) = 3

SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE AQ_FKEY in
(SELECT AQ_FKEY
FROM tblEACodes as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY AQ_FKEY
HAVING Count(*) = 3)

Hope this will work for you.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Michel Walsh]

------------------------
2. HAVING Count = 3 may be working, but:
the number of rows may vary as you can see here:
-------------------------


You may have 26 rows for one AQ_FKEY, but the inner join will remove the
rows NOT IN the list of wanted aqe FKey BEFORE making the count: indeed
INTERSECTION between two sets removes elements (rows) NOT IN one of two
sets.



Vanderghast, Access MVP

 

[John Spencer]

Michel,
I don't understand your comment. Do you mean that the following query will
give the wrong results?

SELECT SK, AQ_FKEY
FROM tblEACodes
WHERE AQ_FKEY in
(SELECT AQ_FKEY
FROM tblEACodes as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY AQ_FKEY
HAVING Count(*) = 3)

Or are you saying that if there were additional values for the combination of
AQ_FKEY and aqeEA_FKEY for example the following. That then you would get
back additional rows.

tblEACodes:
==============================
SK AQ_FKEY aqeEA_FKEY
== ======= ====
10 26 6
21 57 4
23 57 6
42 57 30
54 57 32
55 57 39

The query would then return 5 rows from the above set (all but SK = 10).

If the poster wishes to limit the return to exactly those that have only the
values 4,6, and 30 and no other associated aqeEA_FKEY associated values then
the query does become a bit more complex.

SELECT SK, AQ_FKEY
FROM tblEACodes INNER JOIN
(SELECT AQ_FKEY, Count(*) as CountAll
FROM tblEACodes
GROUP BY AQ_FKey) as Tmp
ON TblEACodes.AQ_Fkey = Tmp.AQ_FKey
WHERE AQ_FKEY in
(SELECT AQ_FKEY
FROM tblEACodes as B
WHERE B.aqeEA_FKEY In (4,6,30)
GROUP BY AQ_FKEY
HAVING Count(*) = 3)
AND Tmp.CountAll = 3


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[candide_sh]

Hello John,

the

In (4,6,30) -part

means these are the minimum criteria to be met. More rows that meet
the criteria are no problem (like your example giving 5 rows for
AQ_FKEY = 57

 

[Michel Walsh]

Using the IN construction as you did, or using an INNER JOIN, if there is
no dup, will produce the right result. My last comment was more about *how*
the inner join was working by throwing away unwanted EA_Fkey. I thought the
OP was having objections, or questions, in that particular matter.


Vanderghast, Access MVP

 

[candide_sh]

Hello to all,

at the end I used John's SQL-statement (message #7) and it works fine.
Thank you for your help