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.lo cation 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.c ontentid
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
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.lo cation 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.c ontentid
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
Comment