By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Checking for mismatch entries in two tables (Urgent Please)

P: 17
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
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Try adding it on your WHERE clause.

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

-- CK
Nov 3 '08 #2

P: 17
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.