How to filter SQL query based on multiple values in the same column

Question

I need to write a query that pulls only employees who are missing their degrees entered into our ERP system. For example, we have a table where their degrees are listed.

Employee ID   FName  LName  Degree
100           John   Smith  BA
200           Bill   Jones  BS
300           Alice  Waters BA
300           Alice  Waters MA
400           Joe    Lewis  MA

They would like me to pull from this table, only Joe Lewis because he doesn't have a bachelors degree entered in the system, but since he has a master's degree, the assumption is he also has a bachelor's, and someone just missed entering it into the system.

I've tried using EXCEPT filtering on Bachelors degrees, however, that still yields

Employee ID   FName  LName  Degree
300           Alice  Waters MA
400           Joe    Lewis  MA

And I don't want Alice in the list because she has a bachelors degree coded into the system.

Any thoughts on how I might approach this would be much appreciated.

hint: find all people with a masters, then subtract off the ones with Bachelors... remaining ones are those of interest. — Randy
– Randy, Commented Sep 26, 2017 at 22:11
Bachelors would be either BS or BA, no? Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. sql-server-2014. Differences in syntax and features often affect the answers. Note that tsql narrows the choices, but does not specify the database. — HABO
– HABO, Commented Sep 27, 2017 at 3:09

Gordon Linoff · Accepted Answer · 2017-09-26 22:13:17Z

2

If this is just for MA and BA, you can use conditional aggregation:

select empid, fname, lname
from t
group by empid, fname, lname
having sum(case when degree = 'BA' then 1 else 0 end) = 0 and
       sum(case when degree = 'MA' then 1 else 0 end) > 0;

Or, you can use exists:

select t.*
from t
where degree = 'MA' and
      not exists (select 1
                  from t t2
                  where t2.empid = t.empid and t2.degree = 'BA'
                 );

answered Sep 26, 2017 at 22:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

noonch Over a year ago

Exists suggestion worked like a charm! Much thanks for everyone's help!

Brett · Accepted Answer · 2017-09-26 23:11:35Z

1

You could go with a left join of the Masters subset against the Bachelors subset:

select m.EmployeeId, m.FName, m.LName 
from      (select * from Employee where Degree in ('MA')) m
left join (select * from Employee where Degree in ('BA', 'BS')) b
on m.EmployeeId = b.EmployeeId
where b.EmployeeId is null

answered Sep 26, 2017 at 23:11

Brett

1,67010 silver badges13 bronze badges

Comments

Stéphane CLEMENT · Accepted Answer · 2017-09-27 07:56:24Z

0

Maybe you should consider to use a query without subqueries...

SELECT E.EmployeeId, E.FName, E.LName 
FROM Employee AS E
LEFT JOIN Employee AS F ON (E.EmployeeId = F.EmployeeId AND F.Degree = 'BA')
WHERE E.Degree <> 'BA' AND F.EmployeeId IS NULL

Or (if BS must be considered as well) :

SELECT E.EmployeeId, E.FName, E.LName 
FROM Employee AS E
LEFT JOIN Employee AS F ON (E.EmployeeId = F.EmployeeId AND F.Degree IN('BA','BS'))
WHERE E.Degree <> 'BA' AND E.Degree <> 'BS' AND F.EmployeeId IS NULL

keep in mind subqueries are often slow, depending on how many row is concerned...

answered Sep 27, 2017 at 7:56

Stéphane CLEMENT

3721 silver badge14 bronze badges

Collectives™ on Stack Overflow

How to filter SQL query based on multiple values in the same column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related