Understanding relationships with a table and itself

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

fname lname ssn superssn
Ahmed Ali 112233 223344
Hanaa Sobhy 123456 223344
Kamel Mohamed 223344 321654
Amr Omran 321654 NULL
Edward Hanna 512463 321654
Maged Raoof 521634 968574
Mariam Adel 669955 512463
Noha Mohamed 968574 321654

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:

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';

Leave a Reply