Comparing data in table with validation table

Hi all

I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...

I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this

thanks

You could do something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode)

etc.

As an aside, I would probably consider having a transition phase where you have a table for data entry, then a table that houses the final data, so you never have bad values in your actual OLTP database, just in the transition data, which you would delete in the process of putting it in the main table.

|||Would the above work if the postcode table contains all possible postcodes but the customer table may contain 1000 off the possible 16000 postcodes.|||

Well, yeah, but you need to be able to form the set of 1000 possible rows that are legal and use it in the EXISTS rather than all rows. So something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode

and meets1000criteria = 1)

Or you could build a view for the valid postalCodes.