NOT IN and Sub Query

[shapper]

Hello,

I have the following Query:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In (SELECT DISTINCT B.ID FROM B WHERE Year
([CreatedDate])=2006))
AND ((A.CODE)="XYZ"));

This query is working.

Can't I move the "SELECT DISTINCT B.ID ..." to a different query?
Something like:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In ([SUBQUERY])) AND ((A.CODE)="XYZ"));

and SUBQUERY would be:

SELECT DISTINCT B.ID
FROM B
WHERE Year([CreatedDate])=2006

I am always asked for the value of SUBQUERY when I run main query.

Thank You,
Miguel

 

[Piet Linden]

Hello,

I have the following Query:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In (SELECT DISTINCT B.ID FROM B WHERE Year
([CreatedDate])=2006))
AND ((A.CODE)="XYZ"));

This query is working.

Can't I move the "SELECT DISTINCT B.ID ..." to a different query?
Something like:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In ([SUBQUERY])) AND ((A.CODE)="XYZ"));

and SUBQUERY would be:

SELECT DISTINCT B.ID
FROM B
WHERE Year([CreatedDate])=2006

I am always asked for the value of SUBQUERY when I run main query.

Thank You,
Miguel

Why even use DISTINCT? - you don't need it.
I would use an outer join instead of a subquery. The performance of
NOT IN is terrible. I think the Find Unmatched query wizard will
build the outer join query for you.

 

[Marshall Barton]

I have the following Query:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In (SELECT DISTINCT B.ID FROM B WHERE Year
([CreatedDate])=2006))
AND ((A.CODE)="XYZ"));

This query is working.

Can't I move the "SELECT DISTINCT B.ID ..." to a different query?
Something like:

SELECT A.ID, A.Name
FROM A
WHERE (((A.ID) Not In ([SUBQUERY])) AND ((A.CODE)="XYZ"));

and SUBQUERY would be:

SELECT DISTINCT B.ID
FROM B
WHERE Year([CreatedDate])=2006

No. SQL treats queries the same as tables and you're asking
if you could write a query to retrieve all fields from table
B by writing:
B
in a query's SQL view.

A usually more efficient way to write your original query
without using a subquery could be:

SELECT A.ID, A.Name
FROM A LEFT JOIN B
ON A.ID = B.ID
WHERE Year(B.CreatedDate) = 2006
AND A.CODE = "XYZ"
AND B.ID Is Null