I have the following Problem :
I am using the BDE together with Delphi.
I have 1 table. One of them is the Master table. The problem is that the master table links to itself to model a hierarchical structure. The structure of the Master table Looks Like this.
Table Name : Employee
Fields : EmpID Name SurName SelfLink Description
: 1 John Smith NULL The Boss
: 2 Mike Madison 1 General Manager
: 3 Achim Bernstein 1 Accountant
: 4 Bob Hickers 2 Sales Departement
: 5 Tom Anderson 2 Advertising Departement
: 6 Max de Kock 4 Exports
This should look like this in a DBGrid or other data aware component :
John Smith : The Boss
Mike Madison : General Manager
Bob Hickers : Sales Departement
Max de Kock : Exports
Tom AnderSon : Advertising Departement
Achim Bernstein : Accountant
The reason for this is as eg. "Mike Madison : General Manager" reports to "John Smith" who is the Boss. Then "Bob Hickers" from the Sales Departement should be displayed as he is next under "Mike Madison". "Achim Bernstein" should be displayed LAST !!! even though he is at a higher level as the 3 people above him , all the people under a person , in this case "Mike Madison" should be displayed first. How would I write an SQL statement that would do this to about 5 levels.