Trying to do a sql subselect

[cowznofsky]

This has got me all confused. I want to get all the values from one
table which are NOT in a related table.

So in regular sql I could do:

select lastName from table1
where lastName not in (select lastName from table2)

But (if I'm seeing straight) this doesn't work in Access. How can I do
this, preferably with standard sql?

TIA,
Jim

 

[Pat Hartman \(MVP\)]

The simplest way is a left join.

Select ...
From tblA Left Join tblB On tblA.fld1 = tblB.fld1
Where tblB.fld1 Is Null;

This only returns rows from tblA that do not have matching rows in tblB.
Just make sure that the tblB field that you check for null is a field that
always has data such as the primary key.

 

[Dirk Goldgar]

This has got me all confused. I want to get all the values from one
table which are NOT in a related table.

So in regular sql I could do:

select lastName from table1
where lastName not in (select lastName from table2)

But (if I'm seeing straight) this doesn't work in Access. How can I
do this, preferably with standard sql?

That ought to work. Have you tried it?

 

[Jamie Collins]

The simplest way is a left join.

An OUTER JOIN would also run orders of magnitude faster.

Jamie.

--

 

[Pieter Wijnen]

The not in problems (slowness) is due to Jet "stupidity"
to use the in operator effectivly in this case you'd have to write

select lastName from table1
where lastName not in (select lastName from table2 where
table2.lastname=table1.lastname)

not in is btw slow for most SQL db's

better is

select lastName from table1
where not exists (select 'X' from table2 where
table2.lastname=table1.lastname)


Pieter

 

[Jamie Collins]

The not in problems (slowness) is due to Jet "stupidity"

not in is btw slow for most SQL db's

I don't think 'stupidity' is the correct way of looking at it; lost
opportunity, perhaps <g>? The best optimizers will be able to tell that
the various constructs are equivalent and will do the same thing under
the covers with each. Does Jet have the best parser/compiler/optimizer
in town? Of course not. Is it 'stupid' or is it a case of GIGO...?

to use the in operator effectivly in this case you'd have to write

select lastName from table1
where lastName not in (select lastName from table2 where
table2.lastname=table1.lastname)

The OP's code worked for me as posted.

better is

select lastName from table1
where not exists (select 'X' from table2 where
table2.lastname=table1.lastname)

Better for what/whom? Not better for Jet. I thought NOT EXISTS might be
faster (i.e. short-circuit as it does on SQL Server) but when I tested
it I found this wasn't the case. Always test <g>! If you find a certain
construct better for maintenance purposes (e.g. it is easier to be
understood by someone who speaks SQL) then you may have a case that
outweighs any performance disadvantage; that would be a hard case to
make with my tests (10K row table, 2 minutes for the NOT EXISTS,
subsecond for the join - I may have fudged the test!)

Jamie.

--