Eliminate hierachical values from Left joins

[nickedw0]

After running a sql query thru a team and left  left joining on a 'ReportsTo' field, I am left with 3 tables. e.g.:

Team table:

id1

id2

id3

id4

Supervisor table:

id2

id4

Manager table:

id4

;

1 How can I eliminate id2 and id4 from the Team table.

2 How can I eliminate Id4 from the Supervisor table?

Thanks for any help

[uacg0009 · Accepted solution]

Hi,

You can get what you want using not exists(). For example:

You have

team_id in Team Table

supervisor_id in Supervisor Table

manager_id in Manager Table

Then you need to :

Load * resident Team_table where not exists(supervisor_id,team_id);

Load * resident Supervisor_table where not exists(manager_id,supervisor_id);

These 2 tables above is what you want.

Please try.

Aiolos Zhao

[nickedw0]

Thanks this produced the subsets I needed. I followed with joins to merge into single table with columns for supervisors and managers.