EXISTS

[Guest]

I can't seem to get EXISTS to work in Access queries... Here's an example...
SELECT S.S, P.P
FROM S, P
where Exists (SELECT DISTINCT SPJ.S, SPJ.P
FROM SPJ);

I expected this to do a Cartesian Product of S and P but then only give me
those S and P combinations that are in SPJ.

I really want those combinations of S and P that are not in SPJ. But if I
do..

SELECT S.S, P.P
FROM S, P
where NOT Exists (SELECT DISTINCT SPJ.S, SPJ.P
FROM SPJ)
;

It returns nothing.

 

[Douglas J. Steele]

I'm unaware of EXISTS being part of Access SQL.

Try joining your cartesian product with SPJ and determine which exist in
both that way.

 

[Brian Camire]

You need a WHERE clause in the subquery to correlate to S and P, and you
don't need the DISTINCT, as in something like:

SELECT
S.S,
P.P
FROM
S,
P
WHERE
NOT EXISTS
(SELECT
NULL
FROM
SPJ
WHERE
SPJ.S = S.S
AND
SPJ.P = P.P)

This assumes S and P are not NULL.

 

[Marshall Barton]

I can't seem to get EXISTS to work in Access queries... Here's an example...
SELECT S.S, P.P
FROM S, P
where Exists (SELECT DISTINCT SPJ.S, SPJ.P
FROM SPJ);

I expected this to do a Cartesian Product of S and P but then only give me
those S and P combinations that are in SPJ.

I really want those combinations of S and P that are not in SPJ. But if I
do..

SELECT S.S, P.P
FROM S, P
where NOT Exists (SELECT DISTINCT SPJ.S, SPJ.P
FROM SPJ)

You have to check if the values found in the subquery agree
with the valus in the main query. Otherwise, any data in
the SPJ table will satisfy the Exists.

. . .
where NOT Exists (SELECT DISTINCT SPJ.S, SPJ.P
FROM SPJ
WHERE SPJ.S = S.S And SPJ.P = P.P)

But, I think Doug is right about it being more efficient to
use Inner Joins instead.

 

[Guest]

Your solution worked beautifully. Thank you. I have a ways to go before I
understand EXISTS. e

 

[Guest]

It is and so is IN. I have found IN to be very useful but I'm still
struggling with EXISTS

 

[Michel Walsh]

Hi,


EXISTS replace IN when your "search" spans more than ONE field:



.... WHERE f1 IN( SELECT g1 FROM somewhere)

is fine, but if you look for the tuple (f1, f2), as example, for some
"natural primary key", you cannot use

.... WHERE (f1, f2) IN ( SELECT g1, g2 FROM somewhere)


but you have to go back to the more generic syntax, involving EXISTS


.... WHERE EXISTS( SELECT * FROM somewhere WHERE f1=g1 AND f2=g2)




Vanderghast, Access MVP