Nested Sub Queries

vpatel · #1 (**permalink**) 05-12-04, 12:54

Hello,

I have a table that contains following user attributes

FirstName

LastName

Title

Region

District

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.

Thanks
V. Patel

r937 · #2 (**permalink**) 05-12-04, 16:33

Code:

select m.FirstName as employeeFirstName
     , m.LastName  as employeeLastName
     , m.Region
     , m.District
     , e.FirstName as employeeFirstName
     , e.LastName  as employeeLastName
  from yourtable m
left outer
  join yourtable e
    on m.Region   = e.Region
   and m.District = e.District       
   and              e.Title = 'Employee'
 where m.Title = 'Manager'  
order
    by 2,1,6,5

then perform some logic while looping over the output to detect the manager name control break, so you can print that nice indented list

Internet Services

Web Development

Digital Marketing

Consumer Tech