How to exclude only one two items but include the rest

Transact-SQL
1

I have a table with the lines of business and state and I want to include everything but MCR PA and MCR NV. I can say != PA because MCD does not have PA but I cannot say != NV because that would exclude MCD too. I was trying (LOB != MCR and Market !=NV) but that exclude all MCR and that was not what I wanted. Here is how the data is.

DATA HAVE
LOB Market
MCD CA
MCR CA
MCD CO
MCR CO
MCD CT
MCR CT
MCD GA
MCR GA
MCD NV
MCR NV
MCD NY
MCR NY
MCD OH
MCR OH
MCR PA
MCD WV
DATA WANT
LOB Market
MCD CA
MCR CA
MCD CO
MCR CO
MCD CT
MCR CT
MCD GA
MCR GA
MCD NV
MCD NY
MCR NY
MCD OH
MCR OH
MCD WV
2 
WHERE (LOB <> 'MCR' AND Market <> 'PA') AND 
              (LOB <> 'MCR' AND Market <> 'NV')

You can also use CONCAT to create a unique column, it's easier to read and easier to understand.

WHERE CONCAT(LOB,'_',MARKET) NOT IN ('MCR_PA','MCR_NV')
2 Likes
3

hi

hope this helps

i noticed an issue with the solution .. please excuse me

create data script

drop table if exists #Data
create table #Data (LOB varchar(10) , Market varchar(10))
insert into #Data select 'MCD','CA'
insert into #Data select 'MCR','CA'
insert into #Data select 'MCD','CO'
insert into #Data select 'MCR','CO'
insert into #Data select 'MCD','CT'
insert into #Data select 'MCR','CT'
insert into #Data select 'MCD','GA'
insert into #Data select 'MCR','GA'
insert into #Data select 'MCD','NV'
insert into #Data select 'MCR','NV'
insert into #Data select 'MCD','NY'
insert into #Data select 'MCR','NY'
insert into #Data select 'MCD','OH'
insert into #Data select 'MCR','OH'
insert into #Data select 'MCR','PA'
insert into #Data select 'MCD','WV'

My Solution

SELECT 
    * 
FROM 
    #Data
WHERE 
   LOB <> 'MCR' 
     AND  
  ( Market <> 'PA' OR  Market <> 'NV')

Solution given
select * from #Data WHERE (LOB <> 'MCR' AND Market <> 'PA') AND (LOB <> 'MCR' AND Market <> 'NV')

image
image762×583 12.5 KB

1 Like
4

You are right @harishgg1, my solution is incorrect.