Recently Equifax fired some of its employees that had second full-time jobs. Equifax knew about this because it has the data on some 100M employees. It is unsettling that Equifax could actually make this into a service for other employers. Nevertheless, it is an interesting SQL question. Given this schema and data
create table employees ( ssn char(1), ein char(1) );
insert into employees (ssn, ein) values
('1','a'),
('1','b'),
('2','b'),
('3','c'),
('3','d'),
('3','e'),
('4','b'),
('4','d'),
('5','b');
What is the query needed to discover who at company EIN has a second job. My solution is to first find all the people with 2 or more jobs
select e1.ssn as ssn, e1.ein as ein1, e2.ein as ein2 from employees e1, employees e2 where e1.ssn = e2.ssn and e1.ein <> e2.ein;
And in this set find the people employed by 'b'
select * from (
select e1.ssn as ssn, e1.ein as ein1, e2.ein as ein2
from employees e1, employees e2
where e1.ssn = e2.ssn and e1.ein <> e2.ein ) t
where t.ein1 = 'b'
I suspect there is a more elegant way, but this was good enough to know it is easy to figure out.