SQL select row if column equal to row

Question

I got this table:

| NINumber  | fname | lname   | mentored_by |
---------------------------------------------
| DD1199XYZ | John  | Smith   | DD234CD1X   |
| DD0094CYY | Barry | Gibbson | DD0094CYY   |
| DD234CD1X | Mike  | Hammer  | NULL        |
| AB2323CYA | Peter | Falk    | NULL        |

Basically I'm trying to find the person that is not mentored by anyone but is a mentor to someone. And display their NINumber, initial of their first name and last name.

For example from my table I should get the person with:

| DD234CD1X | Mike  | Hammer  | NULL        |

Here is what I've tried so far:

SELECT NINumber, SUBSTRING(fname, 1, 1) AS init, lname 
FROM Hospital_Doctor
WHERE NINumber IN (
 SELECT NINumber 
 FROM Hospital_Doctor 
 where IFNULL(mentored_by, 'NULL') = 'NULL' 
)

This basically shows the persons that are not mentored by anyone but I can't figure how am I supposed to show that a person is also a mentor.

Answer 1

You could use exists with a correlated subquery:

select t.*
from Hospital_Doctor t
where 
    t.mentored_by is null
    and exists (
        select 1 from Hospital_Doctor t1 where t1.mentored_by = t.NINumber
    )

Demo on DB Fiddle:

NINumber  | fname | lname  | mentored_by
:-------- | :---- | :----- | :----------
DD234CD1X | Mike  | Hammer | null