"not in" sub query not working

  • Thread starter Thread starter faulknerandy
  • Start date Start date
F

faulknerandy

Hi,

I have a SQL written query that looks like this:

SELECT tblNominal.code
FROM tblNominal
WHERE (((tblNominal.code) Not In (select txtCode from
tblReportGroup)));

As far as I understand things this should list codes from the
tblNominal table that do not appear in the list from the tblReportGroup
table...only it doesn't work!

The query returns nothing, despite the fact that I know for certain
there are rows in tblNominal containing codes that are not in the sub
query.

Can anybody help?

I'm using Access 2003.

Alfie
 
That should work as you describe.

Is the data type and formatting of the two fields identical? Have you got
leading zeros, or trailing spaces or some such thing in one or other of the
tables?
Try making 2 temp tables with a subset of the data in the two actual tables,
and experiment on those. It should be a lot easier to spot the problem if
you only have a few records in each table...

Cheers,

Chris.
 
One possibility would be that one of the txtCode is a Null value. Try
adding the condition "Where txtCode is Not Null" in the subquery.
 
Andy,

IN and NOT IN are notoriously slow in Jet. You'll get better performance by
using a frustrated join.

SELECT tblNominal.code
FROM tblNominal LEFT JOIN tblReportGroup
ON tblNominal.code = tblReportGroup.txtCode
WHERE tblReportGroup.txtCode Is Null

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Or Exists joins...

Pieter

Graham R Seach said:
Andy,

IN and NOT IN are notoriously slow in Jet. You'll get better performance
by using a frustrated join.

SELECT tblNominal.code
FROM tblNominal LEFT JOIN tblReportGroup
ON tblNominal.code = tblReportGroup.txtCode
WHERE tblReportGroup.txtCode Is Null

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...



--
 
So, what is best:

(1)
SELECT SomeId FROM TableA
WHERE NOT EXISTS
(
SELECT * FROM TableB
WHERE TableB.Code = TableA.SomeId
)

OR

(2)
SELECT someId
FROM TableA LEFT JOIN TableB
ON TableA.SomeId = TableB.Code
WHERE TableB.Code Is Null

OR

(3)
SELECT someId
FROM TableA
WHERE someId NOT IN
(
SELECT Code FROM TableB
)

It seems, from a previous post that the third option is the least efficient
(though possibly the easiest to understand)

How about the other two? Is either one preferred over the other? Which is
faster? Or does it depend on the amount of data that you have?

Chris.



"Pieter Wijnen"
 
Back
Top