Imagine a very simple database schema:

CREATE TABLE Employee
(
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL
);

CREATE TABLE Department
(
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    manager_employee_id INTEGER,
    loveliest_jumper_employee_id INTEGER
);

Here, the two ..._employee_id columns in the Department table are foreign keys into the Employee table.

I’d like to get a list of employee information for all employees who are either managers or have the loveliest jumper in their department.

This would do:

SELECT Employee.* 
FROM Employee, Department 
WHERE Employee.id = Department.manager_employee_id
UNION
SELECT Employee.* 
FROM Employee, Department 
WHERE Employee.id = Department.loveliest_jumper_employee_id;

But it’s a bit of a pain. In my case, there are actually four relevant foreign key columns, not just two, so it’s even more redundant. Is there are simpler way to write this query?

Some extra points about my situation:

• Sometimes the foreign key could be NULL (i.e. no manager and/or no lovely jumpers at all in that department) and there should be no row in the result set for that.
• You can assume that there is no foreign key (except NULL) is listed in more than one row or column (no one is is more than one department … and no managers have lovely jumpers).
• I don’t need any information in the result set except the Employee table i.e. neither the department name nor the employee’s relationship with the department (manager / lovely jumper wearer).

>Solution :

SELECT Employee.* 
FROM Employee e
INNER JOIN Department e ON e.id = d.manager_employee_id OR e.id = d.loveliest_jumper_employee_id;