More fun with subqueries

[Dirk]

I had designed a query with subquery that worked fine but lost it after one
of Access famous hiccups. (Function was gone from my code). Upon redesigning
I can't seem to get it to work:

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID] AND FUR.FUR_ID Not In (SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5);

Disecting this:

SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5

Delivers 5 records with all null values.

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID]

Delivers 2 for a certain ID. FUR_ID's are never null since it is a primary
key.
And STILL the Not In operator evaluates that both of the FUR elements have
an ID equal to null apparently.

I am sure I have to be missing something here because logically this should
be impossible.

 

[mscertified]

Include a WHERE IS NOT NULL in the subquery

-Dorian

 

[Dirk]

Thanks a lot. Yes, after a few more tries I realised that is what I had done
before. So should I interpret this behavior as follows?

"Jet evaluates all null valued fields in a subquery to match all rows in the
x IN, x = SOME, ANY, ALL. "

I have been looking on MSDN but haven't been able to find a definitive
documentation about the workings of subqueries. Do you, or does anyone know a
good reference page to subqueries in Jet?
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true is very interesting but far from definitive.

Include a WHERE IS NOT NULL in the subquery

-Dorian

I had designed a query with subquery that worked fine but lost it after one
of Access famous hiccups. (Function was gone from my code). Upon redesigning
I can't seem to get it to work:

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID] AND FUR.FUR_ID Not In (SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5);

Disecting this:

SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5

Delivers 5 records with all null values.

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID]

Delivers 2 for a certain ID. FUR_ID's are never null since it is a primary
key.
And STILL the Not In operator evaluates that both of the FUR elements have
an ID equal to null apparently.

I am sure I have to be missing something here because logically this should
be impossible.

 

[John Spencer]

Actually if you have a null result returned in the subquery and use NOT in then
you will always get a match.

NULL is undetermined, so
In(Null) returns false in this situation
and NOT False = TRUE.

So if your subquery returns even one null value, your Not In (SELECT ...) will
end up being TRUE.

Thanks a lot. Yes, after a few more tries I realised that is what I had done
before. So should I interpret this behavior as follows?

"Jet evaluates all null valued fields in a subquery to match all rows in the
x IN, x = SOME, ANY, ALL. "

I have been looking on MSDN but haven't been able to find a definitive
documentation about the workings of subqueries. Do you, or does anyone know a
good reference page to subqueries in Jet?
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/msjet/jetch04.mspx?mfr=true is very interesting but far from definitive.

Include a WHERE IS NOT NULL in the subquery

-Dorian

I had designed a query with subquery that worked fine but lost it after one
of Access famous hiccups. (Function was gone from my code). Upon redesigning
I can't seem to get it to work:

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID] AND FUR.FUR_ID Not In (SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5);

Disecting this:

SELECT FUR.FUR_ID_CRED_OP
FROM DTB_FACTUURUITREGELS FUR INNER JOIN DTB_FACTUURUIT FCU ON FUR.FCU_ID =
FCU.FCU_ID
WHERE FCU.FST_ID = 5

Delivers 5 records with all null values.

SELECT Count(*) AS Aantal
FROM DTB_FACTUURUITREGELS AS FUR
WHERE FUR.FCU_ID=[ID]

Delivers 2 for a certain ID. FUR_ID's are never null since it is a primary
key.
And STILL the Not In operator evaluates that both of the FUR elements have
an ID equal to null apparently.

I am sure I have to be missing something here because logically this should
be impossible.