SQL select with multiple different conditions

I am still learning SQL and can't find a proper way to find the following information:

I have created a table "employees" with the following columns:

'department', 'age', 'salary', 'bonus';

I am trying to design a query that will give me all employees that have someone the same age as them in another department and with a bonus superior to their salary.

(to be more precise, if someone in department 'SALES' has the same age as someone in department 'RESEARCH' and have a bonus that is superior to that guy in research's salary, then I would like to display both of them)

Is this possible to do in sql?

Thank you for your time,

-Tom

Solution

You can do this using exists. Because you care about the relationship in both direction, this is as simple as looking for people with the same age in the two departments but who do not have the same bonus:

select e.*
from employees e
where exists (select 1
              from employees e2
              where e2.department <> e.department and e2.age = e.age and
                    e2.bonus <> e.bonus
             );

To get the pairs on the same row, use a self-join:

select e1.*, e2.*
from employees e1 join
     employees e2
     on e1.age = e2.age and e1.department <> e2.department and
        e1.bonus > e2.bonus;