IN-Predicate means OR - I need AND

  • Thread starter Thread starter candide_sh
  • Start date Start date
C

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,
 
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
 
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);
 
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.
 
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
 
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.
 
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
 
------------------------
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
 
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
 
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
 
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
 
Hello to all,

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