Exists versus In

R

Rajesh B. Patel

Hi All,

I cannot get a query with the Exists function to work properly. Using
the In function gives the proper results, but it runs too slowly. The
queries are as follows:

(Exists function version)

SELECT tbl_Keywords.* FROM tbl_Keywords
WHERE EXISTS (Select qry_Keywords_By_Participant.KeywordID FROM
qry_Keywords_By_Participant WHERE
tbl_Keywords.KeywordID=qry_Keywords_By_Participant.KeywordID);

(In function version)

SELECT tbl_Keywords.*
FROM tbl_Keywords
WHERE (((tbl_Keywords.KeywordID) In (Select
qry_Keywords_By_Participant.KeywordID FROM qry_Keywords_By_Participant)));

The overall goal of the query is to select all records in tbl_Keywords
that have a corresponding KeywordID in the qry_Keywords_By_Participant
query. Since tbl_Keywords is a superset of qry_Keywords_By_Participant I
should get all the records in qry_Keywords_By_Participant in my results.
When I run the Exists function version I get ALL the records in
tbl_Keywords. When I run the In function version I get the expected results.

A quick experiment in which I created a table from the results of
qry_Keywords_By_Participant and then used the table in place of the
query in the Exists function version produced the correct results.

Any ideas why?

Thanks so much.

raj
 
J

John Vinson

The overall goal of the query is to select all records in tbl_Keywords
that have a corresponding KeywordID in the qry_Keywords_By_Participant
query.
Click to expand...

If that's the goal, why not use a simple JOIN?

SELECT tbl_Keywords.* FROM tbl_Keywords INNER JOIN
qry_Keywords_By_Participant.KeywordID ON
tbl_Keywords.KeywordID=qry_Keywords_By_Participant.KeywordID;

If KeywordID is indexed (as it surely is) this should be very quick.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
R

Rajesh B. Patel

Hi,

Thank you for your reply.

I failed to mention (sorry) that I cannot use an INNER JOIN since
qry_Keywords_By_Participant is a UNION and I need the results to be
editable.

Thanks

raj
 
J

John Vinson

Hi,

Thank you for your reply.

I failed to mention (sorry) that I cannot use an INNER JOIN since
qry_Keywords_By_Participant is a UNION and I need the results to be
editable.
Click to expand...

Then you're quite possibly out of luck, unless you base a MakeTable
query on qry_Keywords_By_Participant. I believe that JET makes any
query inlcuding a UNION query non-updateable (I know it does so with
Totals queries).

Have you tried both the Exists() and In() queries, stopwatch in hand?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
R

Rajesh B. Patel

Then you're quite possibly out of luck, unless you base a MakeTable
query on qry_Keywords_By_Participant. I believe that JET makes any
query inlcuding a UNION query non-updateable (I know it does so with
Totals queries).

Have you tried both the Exists() and In() queries, stopwatch in hand?
Click to expand...

Yes, all UNION queries are definitely non-updateable.

I have tried In() and it is quite slow. I was trying Exists() to see if
it was any faster, but that function does not work at all in this
context. As I mentioned, it gives me ALL the records in tbl_Keywords as
a result.

Also when I experimented I created two small tables of 5 KeywordID
records then I created a UNION query that concatenated the two tables.
When I use this test UNION query in place of qry_Keywords_By_Participant
with Exists(), I get the expected results (only the 10 records that
match the KeywordIDs in my test UNION query). So I am not sure what is
wrong.

Thank you for your help.

raj
 
Top