sql server 2008 - Incorrect City, State, Zipcode Audit -

i trying audit our systems invalid information. want compare companies_address against xro_zips table has city, state, zipcode. can return zip codes not matching between these 2 tables, return incorrect city names , state abbreviations zip codes match between 2 tables.

this have far..

select distinct 'workplace_base' [db_id], c.external_id, c.company, ca.city,     isnull(ca.[state],'') [state], isnull(ca.zip_code,'') zip_code, d.division workplace_base.dbo.companies_address ca    inner join workplace_base.dbo.companies c on ca.company_id = c.company_id    inner join workplace_base.dbo.divisions d on c.owner_division_id = d.division_id    left join workplace_base.dbo.xro_zips z on ca.zip_code = z.zipcode       , ca.[state] = z.[state] z.zipcode null 

any great. thank ahead of time.

this sort of thing should give discrepancies:

select * ( select distinct      'workplace_base' [db_id],      c.external_id,      c.company,      ca.city city1,      z.city city2,      isnull(ca.[state],'') [state1],      isnull(z.[state],'') [state2],      ca.zip_code zip_code1,     z.zipcode zip_code2,     isnull(ca.zip_code,'missing zip') zip_code_check1,      isnull(z.zipcode,'extra zip') zip_code_check2,      d.division,     case when ca.city <> z.city 'city mismatch' else 'ok' end citycheck,         case when ca.[state] <> z.[state] 'state mismatch' else 'ok' end statecheck,     workplace_base.dbo.companies_address ca inner join workplace_base.dbo.companies c on ca.company_id = c.company_id inner join workplace_base.dbo.divisions d on c.owner_division_id = d.division_id full outer join workplace_base.dbo.xro_zips z on ca.zip_code = z.zipcode ) comparison zip_code1 null or zip_code2 null or citycheck <> 'ok' or statecheck <> 'ok'