IN query not working

[John]

Hi

When I run an IN query as below I don't get eh desired results?

SELECT * FROM Table1 WHERE Table1.ID In (SELECT [ID] from Table2)

What is the problem and how to fix it? Am I better of using and equivalent
EXISTS query?

Thanks

Regards

 

[Tom van Stiphout]

On Sat, 5 Dec 2009 13:50:44 -0000, "John" <[email protected]>
wrote:

What is the desired result? I can't tell from just your sql
statement.

-Tom.
Microsoft Access MVP

 

[John]

Hi Tom

When I run below select query it returns 23114 records;

SELECT Table1.*
FROM Table2 INNER JOIN Table1 ON Table2.[ID] = Table1.[ID]

But when I run what I think is the equivalent delete query, it only wants to
delete 11559 records.

DELETE Table1.*
FROM Table1
WHERE Table1.[ID] In (SELECT [ID] from Table2)

Somehow the two queries are not processing the same number of records.

Thanks

Regards

What is the desired result? I can't tell from just your sql
statement.

-Tom.
Microsoft Access MVP

Hi

When I run an IN query as below I don't get eh desired results?

SELECT * FROM Table1 WHERE Table1.ID In (SELECT [ID] from Table2)

What is the problem and how to fix it? Am I better of using and equivalent
EXISTS query?

Thanks

Regards

 

[Ken Snell]

My guess: you have multiple rows in Table2 with the same ID value. Your
first query thus returns duplicate records from Table1 accordingly.

The IN query returns just the Table1 records, without duplication.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hi Tom

When I run below select query it returns 23114 records;

SELECT Table1.*
FROM Table2 INNER JOIN Table1 ON Table2.[ID] = Table1.[ID]

But when I run what I think is the equivalent delete query, it only wants
to delete 11559 records.

DELETE Table1.*
FROM Table1
WHERE Table1.[ID] In (SELECT [ID] from Table2)

Somehow the two queries are not processing the same number of records.

Thanks

Regards

What is the desired result? I can't tell from just your sql
statement.

-Tom.
Microsoft Access MVP

Hi

When I run an IN query as below I don't get eh desired results?

SELECT * FROM Table1 WHERE Table1.ID In (SELECT [ID] from Table2)

What is the problem and how to fix it? Am I better of using and
equivalent
EXISTS query?

Thanks

Regards