The check for NULL has to be in the WHERE clause
Select PrevYear.* ;
from PrevYear ;
left outer join thisyear ;
on PrevYear.mijbrpls = thisyear.mijbrpls ;
and PrevYear.vlgnr_mut = thisyear.vlgnr_mut ;
WHERE thisyear.vlgnr_mut IS NULL
>
>As promised here are my datasamples and the result of your suggested query:
>PrevYear:
>
>mijbrpls vlgnrbsvz vlgnrplsvr vlgnr_mut bdr_srt
>21102187646 1 11 419 VSCPRM
>21102187646 1 11 420 AKPKST
>21102187646 1 11 420 APRV
>21102187646 1 11 420 BNSPRV
>21102187646 1 11 420 INCKST
>21102187646 1 11 420 INCPRV
>21102187646 1 11 420 PLSOP
>21102187646 1 11 420 1STKST
>21102187646 1 11 421 BHRKST
>21102187646 1 11 422 RISPRM
>
>
>ThisYear:
>
>mijbrpls vlgnrbsvz vlgnrplsvr vlgnr_mut bdr_srt
>21102187646 1 11 419 VSCPRM
>21102187646 1 11 421 BHRKST
>21102187646 1 11 422 RISPRM
>
>
>I am doing the suggested query:
>
>Select PrevYear.*;
> from PrevYear;
> left outer join thisyear;
> on PrevYear.mijbrpls = thisyear.mijbrpls ;
> and PrevYear.vlgnr_mut = thisyear.vlgnr_mut;
> AND thisyear.vlgnr_mut IS NULL
>
>
>Result of the query:
>
>mijbrpls vlgnrbsvz vlgnrplsvr vlgnr_mut bdr_srt
>21102187646 1 11 419 VSCPRM
>21102187646 1 11 420 AKPKST
>21102187646 1 11 420 APRV
>21102187646 1 11 420 BNSPRV
>21102187646 1 11 420 INCKST
>21102187646 1 11 420 INCPRV
>21102187646 1 11 420 PLSOP
>21102187646 1 11 420 1STKST
>21102187646 1 11 421 BHRKST
>21102187646 1 11 422 RISPRM
>
>
>As you see there is no difference between the result and previous year. While I expected that the result would return the records where vlgnr_mut =420
>I hope this clarify things....
>
>Thanks beforehand.
--sb--
