Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Smile Unanswered: Nested Sub Queries


    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.

    V. Patel

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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'  
        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 | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts