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.

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 ?

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.

Try the following:

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

Thanks, rnealejr !

That did the trick!