Building tree structure

[Naoise]

I have three tables, I am looking to get a dendritic structure of organisation roles and who they report to.

A sample might be

tblOrganisationRoles
===========================
OrganisationRoleID int | OrganisationID int | Role varchar
1 1000 CEO
2 1000 CTO
3 1000 Tech
4 1000 General
5 1000 CFO

tblOrganisationRoleOrgChart
===========================
OrganisationRoleID int | ReportsToID int
1 1
2 1
3 2
4 2
5 1

tblOrganisationUsers
===========================
OrganisationUserID int | OrganisationID int | OrganisationRoleID | Firstname varchar | Lastname varchar
1 1000 1 Bill Gates
2 1000 2 Tom Crack
3 1000 3 Paul Curran
4 1000 3 Brian Walsh
5 1000 4 John Doe
6 1000 5 Paul Hand

This structure would know to have CEO as the root because of the record in tblOrganisationRoleOrgChart
as CEO reports to himself This will always be the way, after this it is a case of finding out who reports
to CEO and building it up. If 2 roles report to one role as is the case with Tech and General who
report to CTO then then we sort alphabetically, so General appears first then Tech. If I were to sort
the data above I would be trying to get :


CEO
Bill Gates
CFO
Paul Hand
General
John Doe
Tech
Brian Walsh
Paul Curran
CTO
Tom Crack

So the structure is based on Roles and then just sorted alphabetically by Rolename, Name

Any help/pointers appreciated

 

[ca8msm]

You should ask in the database forum for whichever database you are using, and once you have the data show us the format it is in and we can say how best to show it.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Naoise]

I did ask in the database forum first but there were no takers! I think this is probably best solved in the business layer of the application rather than the data access layer so I am basically going to do some JOIN's on the 3 tables and get as much information about each individual and create a User Object for each. So my starting point is an array of user objects.

 

[Naoise]

Having searched the threads on this forum I can see that my problem is very similar to this thread which I see you have contributed to also :

http://www.tek-tips.com/viewthread.cfm?qid=1363575

- I can actually change my schema to this no problems. My question then is following on from

DECLARE @tree TABLE (id int identity(1,1)
                     ,ParentID int
                     ,hasChild bit
                     ,descr varchar(100))

INSERT INTO @tree VALUES(1,1,'Root')
INSERT INTO @tree VALUES(1,1,'Dad1')
INSERT INTO @tree VALUES(1,1,'Dad2')
INSERT INTO @tree VALUES(1,1,'Dad3')
INSERT INTO @tree VALUES(2,0,'Child1')
INSERT INTO @tree VALUES(3,0,'Child2')
INSERT INTO @tree VALUES(4,0,'Child3')
INSERT INTO @tree VALUES(2,0,'Child4')
INSERT INTO @tree VALUES(8,1,'Child4.1')
INSERT INTO @tree VALUES(9,0,'Child4.1.1')

select * from @tree

SELECT a.descr as parent
       ,b.descr as child
    FROM @tree a
     JOIN (select id,ParentID,descr
            from @tree) b
      on b.Parentid = a.id

which produces :

parent | child
=================
Root Dad3
Root Root
Root Dad1
Root Dad2
Dad1 Child1
Dad1 Child4
Dad2 Child2
Dad3 Child3
Child4 Child4.1
Child4.1 Child4.1.1


So I've two questions

1) How to get rid of the record associating Root with Root

2)How would you go about displaying such a recordset into

Root
Dad1
Child1
Child4
Child4.1
Child4.1.1
Dad2
Child2
Dad3
Child3

Thanks for all replies, much appreciated

 

[ca8msm]

For #1 in the above example, adding a where clause to make sure the two items aren't the same seems to work e.g.

WHERE a.descr <> b.descr

For #2, I imagine if you loaded the data into a DataTable, you could use its Select command to get a reference to any child records. I don't have time to knock up an example at the minute but if you get stuck let me know and I'll see if I can come up with one soon.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Naoise]

Sorry if this seems like a bit of an obvious question, does the DataTable object have inbuilt methods that would actually aid me in finding all child nodes, subchild nodes etc of a given node or is it once again straight SQL on the DataTable...if it is straight SQL - this is the bit I am stuck on, how you would recursively display this tree. Any quick pointer would be appreciated just to get started.

Thanks for speedy reply.

 

[ca8msm]

Yes, the DataTable has a built in Select method you can use.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.