Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    Chicago area
    Posts
    3

    Unanswered: Joining 2 Tables

    I am having difficulty with the correct JOIN syntax for retrieving data from 2 SQL tables. The two tables are:

    Table tblA
    ----------
    ID Name
    1916 Al
    1917 Bill
    1918 Carrie
    1919 Ed
    1920 Frank
    1921 Kristin
    1922 Laura
    1923 Mike


    Table tblB
    ----------
    StaffID BossID Level
    1917 1916 1
    1918 1916 1
    1919 1917 1
    1920 1917 1
    1921 1920 1
    1922 1920 1
    1919 1916 2
    1920 1916 2
    1921 1917 2
    1922 1917 2
    1921 1916 3
    1922 1916 3


    Desired Result Table for a
    selected Table tblA ID of 1917
    ------------------------------
    ID Name
    1916 Al
    1918 Carrie
    1923 Mike


    Here's what's going on: Table tblA is a list of people (the ID is unique). Table tblB is a list of reporting relationships among the people in Table tblA. The Level column indicates how far down the org tree they are. So, for example, Ed (1919) reports to Bill (1917) who reports to Al (1916). In table tblB, record 1 shows Bill reporting to Al. Record 3 shows Ed reporting to Bill. And record 7 shows Ed reporting to Al 2 levels up (ie. through Bill).

    The result that I'm trying to generate is all of the people that a selected person could possibly report to. So, for example, if I selected Bill (1917) from Table tblA, the only people he could report to would be Al, Carrie and Mike (since everyone else in Table tblA either reports to Bill at some level or is Bill).

    I've tried (unsuccessfully):

    "SELECT DISTINCT tblA.ID, tblA.Name From tblA LEFT OUTER JOIN tblB ON tblB.StaffID = tblA.ID WHERE tblB.BossID <> " & selID

    where selID is the ID of the person of interest (1917 in the example above).

    Please help me find the right syntax to get the desired resulting table.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I am unable to find a reference to 1923 (Mike) in table b. Do you want to know who 1917 supervises or who 1917 is supervised by ? What is the relationship between bossid and staffid - how does 1917 relate to 1918 and 1923 ?

  3. #3
    Join Date
    Jul 2002
    Location
    Chicago area
    Posts
    3
    Thanks for your response, rnealejr.

    Table tblB is a list of reporting relationships among the people listed in tblA. Mike doesn't report to anyone, and he doesn't have anyone reporting to him. That's why he doesn't appear in tblB. But since he doesn't report to Bill at any level, the user may want to establish a new reporting relationship having Bill report to Mike. That's why Mike should be included in the result table.

    The end user will be looking at a personnel record from tblA (eg, Bill) wanting to assign a new "boss" to that person. I'm trying ot give him a combobox list (the Result Table) of possible bosses. Frank, for example, shouldn't be in that list of Bill's possible bosses because Bill is Frank's boss (4th record, tblB). Kristin can't be Bill's boss either, since Kristin reports to Bill through Frank (5th and 9th records, tblB).

    Also, there is no reporting relationship between 1917 and either 1918 or 1923. That's why 1918 and 1923 should be included in the Result Table for 1917. As it happens, 1916 is already Bill's boss (record 1, tblB), so 1916 is also a candidate boss for Bill.

    Here's how the example organization chart would look:

    1916 Al
    |
    ----1917 Bill
    | |
    | ----1919 Ed
    | |
    | ----1920 Frank
    | |
    | ----1921 Kristin
    | |
    | ----1922 Laura
    |
    ----1918 Carrie

    1923 Mike

    I hope this is clearer. Thanks again for taking a look at my question.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    select id, name from tblA where id not in (select distinct staffid from tblB where bossid = 1917) and id <> 1917

  5. #5
    Join Date
    Jul 2002
    Location
    Chicago area
    Posts
    3

    Smile

    Thanks, rnealejr !

    That did the trick!

Posting Permissions

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