I would like to provide a demo table. The important columns are
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!
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';