Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    55

    Unhappy Unanswered: Union Query Blues

    I have a single table that contains a heirarchy.

    ID NAME SUPERVISOR
    1 Bob
    2 Sue 1
    3 Joe 1
    4 Kim 2
    5 Dave

    The SUPERVISOR value relates back to the ID. I am trying to get a Union query to show me the values based on what is shown.

    For example:
    selecting #1 would show 1,2,3,4
    selecting #2 would show 2, 4
    Selecting #3 would show 3

    and so on...

    My code is showing me too many records... HELP!!!

    TIA

    ' strCurrentUserID is a value obtained from the login
    SELECT ID, NAME, SUPERVISOR
    FROM tblNAMES
    WHERE (((SUPERVISOR)=[strCurrentUserID]))
    UNION SELECT ID, NAME, SUPERVISOR
    FROM tblNAMES
    WHERE ((([SUPERVISOR])=[ID]))
    ORDER BY NAME;

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    No union is needed.... a simple where will do....

    SELECT ID, NAME, SUPERVISOR
    FROM tblNAMES
    WHERE SUPERVISOR=[strCurrentUserID]
    OR ID = [strCurrentUserID]

    Regards

  3. #3
    Join Date
    Feb 2002
    Posts
    55
    But that does not give me the value for person #4 (Kim).

    If I select #1 (Bob), I want to see everyone who works under Bob and everyone who works under those under Bob.

    Thanks...

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thats a hard one if you want to to also handle 4 and or 5 levels depending on the query and or data....

    How about this:
    Code:
    SELECT ID, Naam
    FROM Tabel1
    WHERE ID=1 or Supervisor = 1
    OR Supervisor In (select ID 
                                  from tabel1 
                                 where supervisor=1)
    OR Supervisor In (SELECT ID 
                                  FROM Tabel1 
                                  WHERE Supervisor In (select ID from tabel1 where Supervisor=1))
    OR Supervisor In (Select ID
                                  FROM Tabel1
                                 Where Supervisor In (SELECT ID 
                                                                    FROM Tabel1 
                                                                   WHERE Supervisor In (select ID from tabel1 where Supervisor=1)))
    Regarsd

Posting Permissions

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