Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

SQL Hierachical Display Problem

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
ewu@webmail.co.za

Comments

  • zibadianzibadian 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
    : ewu@webmail.co.za
    :
    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.
  • PerranPerran 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
    : : ewu@webmail.co.za
    : :
    : 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
  • zapperonzapperon 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
    : : : ewu@webmail.co.za
    : : :
    : : 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
    ewu@webmail.co.za

  • PerranPerran 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
    : : : : ewu@webmail.co.za
    : : : :
    : : : 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
    : ewu@webmail.co.za
    :
    :
    That's too bad. I'll turn on the machine and email it to the email address in your sig.
  • zapperonzapperon 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
    : : : : : ewu@webmail.co.za
    : : : : :
    : : : : 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
    : : ewu@webmail.co.za
    : :
    : :
    : 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
    ewu@webmail.co.za

Sign In or Register to comment.