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.