sort out table A record not exist in table b

[Tony WONG]

i wish to compare 2 tables (one is new, one is old)

This is 1 level comparison, the script works
**********
select A, B, C
from table X
where A not in (select A from table Y)
*************

however i wish to have 2 levels comparison, like this
select A, B, C
from table X
where A, B not in (select A, B from table Y)

but it seems the script should not be written at such, i fail to run it.

could anyone assist? Thanks.

tony

 

[Michel Walsh]

Hi,


You use an EXISTS construction, or an outer join


SELECT a, b, c
FROM x
WHERE NOT EXISTS( SELECT *
FROM y
WHERE x.a=y.a AND x.b=y.b)

Note you can also type something like:

SELECT a, b, c
FROM x
WHERE x.b NOT IN(SELECT y.b FROM y
WHERE x.a=y.a)



but I would personally use the outer join:



SELECT x.*
FROM x LEFT JOIN y
ON x.a=y.a AND x.b=y.b
WHERE y.a IS NULL



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

SELECT A, B, C
FROM table X
where A not in (select A from table Y)
and B not In (Select B From Table Y)

OR
select A, B, C
from table X
where A & B not in (select A & B from table Y)

OR
SELECT TableX.*
FROM TableX LEFT JOIN TableY
ON TableX.A=TableY.A and
TableX.B = TableY.B
WHERE TableY.A is Null or TableY.B is Null

 

[Tony WONG]

Thanks a lot

i have not thought so many ways to do it. sometimes need to think
reversely.

tony

 

[Michel Walsh]

Hi,


The first proposition does not bind the values to be from the same record,
as in example,

TableY={red, apple} ,{yellow, banana}

and

TableX ={red, banana};

you will find that since tableX.color is IN tableY.color and tableX.fruit is
IN tableY.fruit,

then conclude erroneously that tableY has a mention of {red, banana}, which
is not, evidently.


Vanderghast, Access MVP