I have a table that contains following user attributes
The table basically lists all the employees and managers in an organization. Each manager has the Title attribute set to "Manager" and each employee has it set to "Employee". Each manager is assigned to a Region and District.
So lets say that an employee has a region = north, and district = 100, then that employee reports the manager with same region and district attributes.
I want to write a query that will allow me the final output to look something like this:
Jon Doe, Manager - District 10, Region North
Jim Jackson, Employee reporting to Jon Doe
Mary Jones, Employee reporting to Jon Doe
Robert Smith, Manager - District 15, Region East
Bobbie Sue, Employee reporting to Jon Doe
Richard James, Employee reporting to Jon Doe
... ... ... so on ... ... ...
I would like to accomplish this in 1 query (nested sub queries are ok) if possible.
I would appreciate if someone could help me out with the sql part of this. I tried several different things and by no means I am a DB person.
select m.FirstName as employeeFirstName
, m.LastName as employeeLastName
, e.FirstName as employeeFirstName
, e.LastName as employeeLastName
from yourtable m
join yourtable e
on m.Region = e.Region
and m.District = e.District
and e.Title = 'Employee'
where m.Title = 'Manager'
then perform some logic while looping over the output to detect the manager name control break, so you can print that nice indented list