Query with multiple Exists seems to Hang

  • Thread starter Thread starter Jeff Ballance
  • Start date Start date
J

Jeff Ballance

I have three tables that I am selecting from using a
single select statment. The tables are:

P
MID -- Unique identifier
Date -- date field
other columns

T
MID -- links to P table
EID -- link to N table NID column entry
FID -- link to N table NID column entry

N
NID -- Unique identifier
Name -- column to search.

I had written a SQL query that works with SQL 2000, but
when I ran it with Access, the program seemed to just hang
though my progran call the Jet driver or by entering the
query into Access directly. The query is below and uses
two EXISTS because I only need to know that the Name
exists (there may be multiple entries). The SQL statement
that failed is:

Select distinct Top 500 * From P Where P.Date >=
#2/12/2003# and P.Date <= #2/12/2004# and exists( select *
from T where exists (select * from N where (N.Name like '%
Jeff%' and P.MID = T.MID and (T.EID = N.NID or T.FID =
N.NID)))) Order by P.Date;

This statement works fine with SQL and fails with Access
or JET through my program. If I change the order of the
EXISTS, it works fine i.e. the following works:

Select distinct Top 500 * From P Where P.Doc_Date >=
#2/12/2003# and P.Doc_Date <= #2/12/2004# and exists(
select * from N where N.Name like '%John%' and exists
(select * from T where (P.MID = T.MID and (T.EID = N.NID
or T.FID = N.NID)))) Order by P.Doc_Date;

Why is this and is there something that I am missing or
should change more?

Thank you,
Jeff Ballance
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why don't you just INNER JOIN all the tables? Seems more correct.

(JET SQL - untested)

SELECT DISTINCT TOP 500 *
FROM P INNER JOIN (N INNER JOIN T ON N.NID = T.EID OR N.NID = T.FID)
ON P.MID = T.MID
WHERE P.Doc_Date BETWEEN #2/12/2003# and #2/12/2004#
AND N.Name LIKE '*John*'
ORDER BY P.Doc_Date

I've found that multiple equivalancies expressions (ON N.NID = ... OR
.... etc.) sometimes slow down the query - sometimes VERY long.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCyFK4echKqOuFEgEQI1fgCfSmud4/pc7dGZoK2wZQuLaXoZMc0Anizz
9kBSgWdjj4VUdVHysqwhb5Uy
=lnHE
-----END PGP SIGNATURE-----
 
Back
Top