How do I recursively select rows where values in one column are present in another column?

Question

I have a table like below with two columns Person and Supervisor.

Person	Supervisor
1	3
2	3
3	4

I want to select rows where the Supervisor column only has values that are also present in the Person column.

I tried to do it with a simple:

SELECT * FROM table 
WHERE table.Supervisor
IN (SELECT table.Person FROM table)

This leaves me with:

Person	Supervisor
1	3
2	3

The row with Person = 3 and Supervisor = 4 is removed as 4 isn't a value for a person in any of the rows.

However, the resulting table is still incorrect as the two remaining values have Supervisor = 3 which is now not present in the Person column.

Is there a way to perform this task recursively in SQL and be left with the values which satisfy the condition that the values in the Supervisor column should be present in the Person column?

Answer 1

As per the comments there is no need for recursion. You can use a CTE or a temporary table but the trick is to first "remove" any rows where the supervisor is not on the Person column - you can do that as you already have or you can use a self-join and ignore NULL values

select a.* from person as a
left outer join person as b on a.Supervisor = b.Person
where b.Person is not null

Put that into a CTE so you can reference it again and use what is essentially your original query, just referencing the CTE instead of the table i.e.

with cte as 
(
    select a.* from person as a
    left outer join person as b on a.Supervisor = b.Person
    where b.Person is not null
)
select * from cte
where Supervisor in (select Person from cte);

Note that this works in both MySQL and SQL (MSSQL) - you really must take care to tag your questions correctly