Pulling another row if it meets criteria from the first pulled row

Need to compare some accounts, which some people have two of, an admin account and a regular account. These admin accounts list their regular account as their manager and all of the admin accounts start with a 1. How would I make it pull the row of the manager as well as the row with the admin account?

I’ve tried using Where and Where Exists, I can’t seem to think of a way of using Select to get the manager as well, a bit of tunnel vision I think right now.

SELECT *
FROM 
    view
WHERE id like '1%'

The expected result should pull every account beginning with 1 and their managers.

So if Bob has two rows in the account table, one being named 1Bob and the other being named Bob, and 1Bob lists Bob as the manager in the 1Bob manager column, the result should pull 1Bob and Bob

Input
+--------+-------+---------+
| Number |  ID   | Manager |
+--------+-------+---------+
|      1 | 1Bob  | Bob     |
|      2 |  Bob  | Cindy   |
|      3 | Cindy | Derek   |
+--------+-------+---------+

Result

+--------+------+---------+
| Number |  ID  | Manager |
+--------+------+---------+
|      1 | 1Bob |  Bob    |
|      2 |  Bob | Cindy   |
+--------+------+---------+

Answer

You can use an exists clause:

select v.*
from view v
where v.id like '1%' or
      exists (select 1 from view v2 where v2.manager = v.id and v2.id like '1%');

Advertisement

Answer