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')
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
@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
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.