Find the manager of a manager in the same table

The code below works however I need results 1 level up where [~users].[manager] -> manager is 'John Smith'

All users + managers are stored in ~users table, so do I need a self join/recursive CTE? any pointers appreciated

SELECT * FROM [Production01].[dbo].[~assets]
  inner join [~problem] on [~assets].number = [~problem].number
  inner join [~users] on [~problem].opened_by_id = [~users].id
  inner join [~users_group] ON [~problem].[group_name] = [~users_group].[name]
  
  WHERE 
  [~problem].[opened_by_id] IN (SELECT [~users].[id] FROM [~users] WHERE [~users].[manager]= 'John Smith')

hope this helps

Is your aim strictly to find manager of a manager? if so why are you joining to assets, problem and users_group? Also this is worrisome

[~users].[manager]= 'John Smith'

why is the manager column a varchar field? What if John Smith changes his name to Juan Herrera

declare @sample table(id int, name varchar(50), manager varchar(50))

insert into @sample
select 1, 'Uno', 'Tres' union
select 2, 'Dos', 'Uno' union
select 3, 'Tres', null union
select 4, 'Quatro', 'Kabul' union
select 5, 'Kabull', null


SELECT nm.name as [Person],
       m.name as Manager
       
 FROM @sample nm 
 left join @sample m on m.name = nm.manager

Thanks for the replies

@yosiasz - im new to SQL so have it as a varchar for readability. Not looking strictly at manager details, as this is only a snippet of where I'm stuck. The full query is using the all tables to help filter out results.

e.g Select columns from ~Asset details if the ~problem is raised by users where the Managers Manager is x

Looks like both example and link are checking the immediate manager only?

Alternative solution - to get a list of users_id of everyone that reports into "John Smith" no matter how far down the hierarchy? then check if [Problem].opened_by_id exists in that list

Apologies if I've misunderstood the answers.

Since you are new to SQL - might I recommend that you don't prefix your tables, and really don't use a 'special' character to prefix such as ~ or - or _. All that really does is make it much harder to code and I don't see any benefits gained by using the prefix.

1 Like