IN query not working

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
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


Tom van Stiphout said:
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
Click to expand...
Click to expand...
 
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/


John said:
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


Tom van Stiphout said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top