468,236 Members | 1,981 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,236 developers. It's quick & easy.

Checking for mismatch entries in two tables (Urgent Please)

There are two tables in our database Businessmaster and locationmaster
Businessmaster has the following fields like contentid, businessname, businesstype, location, city, state, country, zipcode, citylocid, statelocid and the locationmaster has the fields like contentid, location, city, state, country and I need to fetch all those mismatch records inner joining Businessmaster table and locationmaster table in which city and state are not the same in both the tables.

Here is the query which I have written

select b.contentid as Bcontentid, b.businessname as Bbusnessname, b.businesstype as Bbusinesstype , b.location as Blocation,
b.city as Bcity, b.state as Bstate, b.country as Bcountry,
b.zipcode as Bzipcode, b.crdate Bcrdate, b.isapproved Bisapproved, b.titleurl as Btitleurl, b.countrylocid Bcountrylocid,
b.statelocid as Bstatelocid, b.citylocid Bcitylocid,
l.contentid as Lcontentid,l.location as Llocation, l.city as Lcity, l.state as Lstate, l.country as Lcountry
from businessmaster b inner join locationmaster l
on b.citylocid = l.contentid
where b.isapproved=1
order by contentid, city, state, country

The above query fetches all those records from businessmater and locationmaster where b.citylocid=l.contentid
but I want to fetch only those mismatch records where the city and state mismatches for the given b.citylocid or l.contentid

In other words if the city given in Businessmaster is 'chennai' but the city in location master for the same contentid is reflected as 'bangalore' or if the state
given as 'tamilnadu' in Businessmaster and the state is reflected as 'Andhra Pradesh' in locationmaster then I should get only these records which are mismatching in terms of city and state for the given contentids or citylocids

Results should be displayed as given below fetching only the mismatch records (mismatch records of city and state)

b.city b.state b.country b.citylocid l.contentid chennai Tamil nadu India 274026 274026

l.city l.state l.country
madurai Tamil Nadu India
Nov 3 '08 #1
2 2296
2,878 Expert 2GB
Try adding it on your WHERE clause.

Expand|Select|Wrap|Line Numbers
  2. where b.isapproved=1 and (b.city <> l.city or b.state <> l.state)
Happy coding!

-- CK
Nov 3 '08 #2
Thank you for your reply. I will try the query
Nov 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Advocated | last post: by
3 posts views Thread by eSolTec, Inc. 501(c)(3) | last post: by
4 posts views Thread by Jeff | last post: by
3 posts views Thread by Bruce Lawrence | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.