SQL Hierachical Display Problem - Programmers Heaven

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# SQL Hierachical Display Problem

Posts: 88Member
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
eg.
: 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
Bob Hickers : Sales Departement
Max de Kock : Exports
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.

Zapperon
[email protected]

• Posts: 6,349Member
: 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
: eg.
: : 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.
:
: Zapperon
: [email protected]
:
I cannot give you an SQL statement as such, but I can give you an idea of how to tackle this problem.
First you need to set the rules, which position is above which. This can best be a simple integer field linked to the position (ie. Boss = 1; General Manager=2; etc.). Now you can add a calculated field to each employer, which will give that rank number. Finally you can use SQL to sort the selection based on that field.
Hint: you could also set up a second sort-filter, for example name-alphabetical, in case two pearsons share a position.
• Posts: 241Member
: : 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
: : eg.
: : : 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.
: :
: : Zapperon
: : [email protected]
: :
: I cannot give you an SQL statement as such, but I can give you an idea of how to tackle this problem.
: First you need to set the rules, which position is above which. This can best be a simple integer field linked to the position (ie. Boss = 1; General Manager=2; etc.). Now you can add a calculated field to each employer, which will give that rank number. Finally you can use SQL to sort the selection based on that field.
: Hint: you could also set up a second sort-filter, for example name-alphabetical, in case two pearsons share a position.
:
You could try a data-aware treeview component. That numeric field you're using would work perfectly to identify the parent of the node. I downloaded a free one awhile back which worked well, but it's on my Windoze box which I don't really want to turn on :-). I think it came from diaita.com. If that's not the name, it's close. I might have found it on the components section of this web site. HTH
• Posts: 88Member
: : : 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
: : : eg.
: : : : 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.
: : :
: : : Zapperon
: : : [email protected]
: : :
: : I cannot give you an SQL statement as such, but I can give you an idea of how to tackle this problem.
: : First you need to set the rules, which position is above which. This can best be a simple integer field linked to the position (ie. Boss = 1; General Manager=2; etc.). Now you can add a calculated field to each employer, which will give that rank number. Finally you can use SQL to sort the selection based on that field.
: : Hint: you could also set up a second sort-filter, for example name-alphabetical, in case two pearsons share a position.
: :
: You could try a data-aware treeview component. That numeric field you're using would work perfectly to identify the parent of the node. I downloaded a free one awhile back which worked well, but it's on my Windoze box which I don't really want to turn on :-). I think it came from diaita.com. If that's not the name, it's close. I might have found it on the components section of this web site. HTH
:

Could we make some arrangements to connect in a way so that I could also get the component as the website of Diaita.com looks very very dead to me the link to the TDBTreeView goes nowhere.

Zapperon
[email protected]

• Posts: 241Member
: : : : 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
: : : : eg.
: : : : : 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.
: : : :
: : : : Zapperon
: : : : [email protected]
: : : :
: : : I cannot give you an SQL statement as such, but I can give you an idea of how to tackle this problem.
: : : First you need to set the rules, which position is above which. This can best be a simple integer field linked to the position (ie. Boss = 1; General Manager=2; etc.). Now you can add a calculated field to each employer, which will give that rank number. Finally you can use SQL to sort the selection based on that field.
: : : Hint: you could also set up a second sort-filter, for example name-alphabetical, in case two pearsons share a position.
: : :
: : You could try a data-aware treeview component. That numeric field you're using would work perfectly to identify the parent of the node. I downloaded a free one awhile back which worked well, but it's on my Windoze box which I don't really want to turn on :-). I think it came from diaita.com. If that's not the name, it's close. I might have found it on the components section of this web site. HTH
: :
:
: Could we make some arrangements to connect in a way so that I could also get the component as the website of Diaita.com looks very very dead to me the link to the TDBTreeView goes nowhere.
:
: Zapperon
: [email protected]
:
:
That's too bad. I'll turn on the machine and email it to the email address in your sig.
• Posts: 88Member
: : : : : 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
: : : : : eg.
: : : : : : 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.
: : : : :
: : : : : Zapperon
: : : : : [email protected]
: : : : :
: : : : I cannot give you an SQL statement as such, but I can give you an idea of how to tackle this problem.
: : : : First you need to set the rules, which position is above which. This can best be a simple integer field linked to the position (ie. Boss = 1; General Manager=2; etc.). Now you can add a calculated field to each employer, which will give that rank number. Finally you can use SQL to sort the selection based on that field.
: : : : Hint: you could also set up a second sort-filter, for example name-alphabetical, in case two pearsons share a position.
: : : :
: : : You could try a data-aware treeview component. That numeric field you're using would work perfectly to identify the parent of the node. I downloaded a free one awhile back which worked well, but it's on my Windoze box which I don't really want to turn on :-). I think it came from diaita.com. If that's not the name, it's close. I might have found it on the components section of this web site. HTH
: : :
: :
: : Could we make some arrangements to connect in a way so that I could also get the component as the website of Diaita.com looks very very dead to me the link to the TDBTreeView goes nowhere.
: :
: : Zapperon
: : [email protected]
: :
: :
: That's too bad. I'll turn on the machine and email it to the email address in your sig.
:

Thank You
Now to get to work with Delphi 7

Zapperon
[email protected]