Identifying missing rows with outer join

[JonathanHolliday]

Hi,

I'm trying to identify where some records may be missing from a file using a shared key field.

For illustration tables 'movements' and 'ledger' should both have records for every movement with a key value of 'journal'

To show all details I would use sql:
select * from movements, ledger
where movements.journal = ledger.journal

I have a problem where some records are missing so I might use the code:
select * from movements,
outer ledger
where movements.journal = ledger.journal

BUT how do I show only those records with a missing ledger record (i.e. ledger.journal is null)?

Thanks,
Jonathan.

 

[cookiespin]

You can select into a temp table, then select records from the temp table where ledger.journal is null.

 

[JonathanHolliday]

Thanks,

I tried that but got an error, I think it doesn't help dealing with multi-million row tables so I wanted an exception only solution.

I also realised that I could use the "where not exists" statement as well.

Thank you.