I would like to provide a demo table. The important columns are Fname, Lname, SSN and SuperSSN.

Now, the task is:

Find all names of the employees who directly supervised by Kamel Mohamed.

I couldn’t wrap my head around a table with a relationship with itself.

This is my best solution, but I feel like I can do better:

SELECT *
FROM employee
WHERE superSSN = 223344 

which is correct, but I don’t want to look in the table and get Kamel’s SSN. Is there a shortcut that I can access the employees that have Kamel as their supervisor without directly getting Kamel’s SSN?
If so, please share!

>Solution :

As you know supervisor’s name, then you could – for example – use a subquery:

select *
from employee
where superSSN = (select SSN
                  from employee
                  where fname = 'Kamel'
                 );
                 

or a self-join:

select a.*
from employee a join employee b on a.superSSN = b.ssn
where b.fname = 'Kamel';