CODEWITHSUNDEEP
sql
olivia G
olivia G

Reputation: 11

SQL how to filter a column with one value but not the other

I want to write a query that would be able to look at two columns of a table, an example below:

Table Name "Payroll"

Check_No    Control_code  
123456  PTS
123456  PT1012
123456  EAR 1012
123456  PTO0
123456  PTS
78910   PTS
78910   PTS
78910   PTO0
78910   PT102
78910   PT102
78910   PTO0
89456   PTS
89456   EAR 1012
89456   EAR 1012
89456   PT1012
89456   PTO0
89456   PTO0

For this table, first column is the check number, one check number can have multiple control_code. I want the query to look at the "Control Code" column, for one check number if "control_code" PTS exists but EAR 1012 not, then give me the check number.

In this example, the answer should be 78910

Please help.

Below are the queries that I have tried:

Select [Check_No]
from [Payroll]
Where [Control_Code]<>'EAR 1012'
And [Control Code]='PTS'
Order by [Check_No]

Also:

Select [Check_No]
into #temp
from [payroll]
Where [Control_Code]='EAR 1012'

Select [Check_No]
from [payroll]
where [Check_No] not in 
(Select [Check_No] from #temp)

Upvotes: 1

Views: 318

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use not exists:

select p.*
from payroll p
where p.control_code = 'PTS' and
      not exists (select 1
                  from payroll p2
                  where p2.Check_No = p.Check_No and
                        p2.control_code = 'EAR 1012'
                 );

Upvotes: 1

Related Questions