Dear Yehong,
First, I aplogize for the length of this post.
The problem that you have is that there is no "key" to indicate that an x level employee somehow reports up the line to ID 100, in other words there is no Root Parent key, and that's where the hierarchical grouping option in Crystal came in handy for you. When you say, I only want those related to Employee ID 100, there is nothing to select those records that may be related in a Parent/grandchild/great grandchild ... relationship.
When you left outer join from Table 1 to copy of table 1 aliased as Table 2, you will see all the direct reports per supervisor Id, but you would not see grouped with that employee id those who report to someone who reports to id 100.
Here is a visual example of what I mean
Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 100
Employee D Id 103 Sup ID 102
Now, hierarchically you would like to see that presented as:
Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 101
Employee D Id 103 Sup ID 102
However, with a left outer join from Table A to Table B on Employee ID to Sup ID you would see:
Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 101
Employee C Id 102 Sup ID 101
Employee D Id 103 Sup ID 102
Now, when you go to do your selection criteria, what you are trying to do is slect all employees who may be somehow (child, grandchild, great grandchild and so on) related to the Supervisor.
So, in your parent child relationship as indicated in your fields, there is no field that exists that indicates this relationship above from employee d to employee a, by a simple selection criteria.
If you know the maximum number of levels down the tree in your org, you could alias that number of tables doing left outer joins from Employee id to Sup Id to each one on down the line...
I believe the only way to do what you need to do is to create a stored procedure that "walks the hierachical tree" and stores that data in a temp table from which you then select those that are related. Which is what I have done when a similar need arose for a Subject to Parent Subject relationships for a Call Center application.
I know that this doesn't exactly help you, but it may help you to understand the issue and to explain it to your dba to get what is needed. Here is a link that helped me to understand and code my stored procedure to accomodate this need.
A google on SQL +"Hierarchical Tree" will give you many more references.
Regards,
ro
Rosemary Lieberman
rosemary@microflo.com,
Microflo provides expert consulting on MagicTSD and Crystal Reports.
