Very difficult SQL query

Question

For the following table definitions:

Name                            Null?    Type           Comments
 ------------------------------- -------- ----          ------------------------------------
 ENUM                            NOT NULL NUMBER(4)     ENUM should not exceed a length of 4.
 ENAME                                    CHAR(15)       
 ADDRESS                                  CHAR(25)      ADDRESS should not exceed 25 characters.
 SALARY                                   NUMBER(5)     
 OFFICE                                   CHAR(4)
 DNUM                            NOT NULL NUMBER(4)     Department which this employee belongs to

department

 Name                            Null?    Type          Comments
 ------------------------------- -------- ----          -------------------------------------
 DNUM                            NOT NULL NUMBER(4)
 DMGR                            NOT NULL NUMBER(4)     Department manager
 DNAME                           NOT NULL CHAR(15)

project

 Name                            Null?    Type          Comments
 ------------------------------- -------- ----          -------------------------------------
 PNUM                            NOT NULL NUMBER(4)
 PMGR                            NOT NULL NUMBER(4)     Project manager
 PTITLE                          NOT NULL CHAR(15)

emp_proj

 Name                            Null?    Type
 ------------------------------- -------- ----
 PNUM                            NOT NULL NUMBER(4)
 ENUM                            NOT NULL NUMBER(4)

I have to write SQL query which will find the names of employees who do not share an office but work on the same project, and have different salaries... I've spent last three days trying to figure out something, but no idea as far. I will appreciate any advice.

Answer 1

It might be easiest with all implicit joins (this only checks that they work on at least one of the same projects - working on all the same projects is a slightly different problem, not quite as simple) :

SELECT *
FROM emp AS emp1
     ,emp_proj AS empproj1
     ,emp AS emp2
     ,emp_proj AS empproj2
WHERE emp1.ENUM = empproj1.ENUM
      AND emp2.ENUM = empproj2.ENUM
      AND emp2.ENUM <> emp1.ENUM -- not joined to same employee
      AND empproj1.PNUM = emp_proj2.PNUM -- same project
      AND emp1.OFFICE <> emp2.OFFICE -- not same office
      AND emp1.SALARY <> emp2.SALARY -- not same salary

Note that this will produce symmetric duplicates as well as duplicates due to working on multiple matching projects.

Answer 2

select e1.ename, e2.ename from
    employee e1 join employee e2 
        on (e1.salaray != e2.salary AND e1.office != e2.office)
        join emp_proj p1 
            on (e1.enum == p1.enum)
            join emp_proj p2
                on (e2.enum == p2.enum)
where p1.pnum == p2.pnum

This will return 2 results for each set of names but with positions flipped around. e.g. "Joe and Jack" and "Jack and Joe". Since this is homework, it would be a good exercise for you to figure out how to change the query to not return duplicates.