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.

Thanks

- Jeff

-- Jeff G (jeffg22@mindspring.com), July 02, 2002