I have a 2 tables with data like
first table:
business type:
1.Dealers / Retailers
2.Manufacturers / Producers
3.Distributors/Wholesellers
4.Stockist
second table :
1.Dealers / Retailers
2.Manufacturers / Producers
3.Distributors/Wholesellers
I want to get the data which is in first table and which is not in second table
result: Stockist
query: select businesstype from businesstype where businesstype not in(select business_type from dmsnew..businesstype)
or
by using except keyword
select businesstype from businesstype
except
select business_type from dmsnew..businesstype
example:
insert into assignment(location_id,AssignedBy,AssignedTo,systemname,ipaddress,AssignedToRoleID)select Location_Id,’ND2204′,’ND7777′,’localhost’,’127.0.0.1′,roleid from (select distinct top 2 l.Location_Id,’1′ as roleid from dmsmaster m (nolock) inner join dmslocation l (nolock) on m.master_id=l.master_id left outer join assignment assign (nolock) ON l.location_id=assign.location_id left outer join category_transaction c (nolock) on l.location_id=c.location_id and c.status not in(‘D’) left outer join finalcat f (nolock) on c.level3code=f.level3code and c.book=f.book left outer join area a (nolock) on l.area_id=a.areaid where l.city in(select assigned_city from city_assigned where empid=’ND2204′) and l.location_id not in(select distinct location_id from assignment where assignedtoroleid=’1′) except select location_id,’1′ from assignment where assignedtoroleid=1)tble
