Solved

Select with heirarchy

Posted on 2011-02-17
9
207 Views
Last Modified: 2012-05-11
I have a table with the following columns (among others)

(uid)   firstName   lastName    reportsToID

ALL users are in this table and the reportsToID equates to a user in the same table's uid.

I need a select that shows the org chart based on order by reportsToLastname, lastName, firstName

So...
(uid)   firstName   lastName    reportsToID
1           Ben           Smith               32   (Sam Snead)
2           Tom           benson            32   (Sam Snead)
3           Sharon       Watson          121  (Bill Bixby)
etc....

Would be returned
id         rid      fullName
121     NULL   Bill Bixby
3          121     Sharon Watson
32       Null      Sam Snead
2          32        Tom Benson
1           32       Ben Smith

0
Comment
Question by:lrbrister
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

by:kemi67
ID: 34916126
Supposing that your table is named tmp, use the following query

SELECT     tmp.ID, tmp.reportsToID, tmp.firstName, tmp.lastName
FROM         tmp LEFT OUTER JOIN
                      tmp AS tmp_1 ON tmp.reportsToID = tmp_1.ID
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34916593

Ok,

lets call the table #TMP at this moment (==> see script )

SELECT     Person.ID, Person.FirstName, Person.lastname,
                   reportsTo.ID, reportsTo.FirstName, reportsTo.lastname
FROM         #tmp as Person
LEFT OUTER JOIN #tmp AS reportsTo
ON Person.reportsToId = reportsTo.ID

full example code :
 
create table #tmp
(	Id int, 
	lastname nvarchar(255),
	FirstName nvarchar(255),
	reportsToId int
)

insert into #tmp select 1,'Ben','Smith',32
insert into #tmp select 2,'Tom','benson',32
insert into #tmp select 3,'Sharon','Watson',121 
insert into #tmp select 32,'Sam','Snead', null
insert into #tmp select 121,'Bill','Bixby', null

SELECT     Person.ID, Person.FirstName, Person.lastname,
			reportsTo.ID, reportsTo.FirstName, reportsTo.lastname
FROM         #tmp as Person 
LEFT OUTER JOIN #tmp AS reportsTo 
ON Person.reportsToId = reportsTo.ID


select * from #tmp

drop table #tmp

Open in new window

0
 
LVL 10

Accepted Solution

by:
John Claes earned 350 total points
ID: 34916617
I'm sorry
I forgot the order by clause


order by case when reportsTo.ID is null then Person.ID else reportsTo.ID end desc, Person.ID desc



so it becomes

SELECT     Person.ID, Person.FirstName, Person.lastname,
                  reportsTo.ID, reportsTo.FirstName, reportsTo.lastname
FROM         #tmp as Person
LEFT OUTER JOIN #tmp AS reportsTo
ON Person.reportsToId = reportsTo.ID
order by case when reportsTo.ID is null then Person.ID else reportsTo.ID end desc, Person.ID desc
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34916620
121      Bixby      Bill      NULL      NULL      NULL
3      Watson      Sharon      121      Bixby      Bill
32      Snead      Sam      NULL      NULL      NULL
2      benson      Tom      32      Snead      Sam
1      Smith      Ben      32      Snead      Sam
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:lrbrister
ID: 34916706
Hey guys,
  Looks like poor-beggar is more complete and gives me things in the correct order.

I AM able to use kemi67: with some slight modifications

You guys have any objection to a soplit with most to poor_beggar: and a nod to kemi67:?
0
 
LVL 7

Assisted Solution

by:kemi67
kemi67 earned 150 total points
ID: 34916712
Ops, it seems i missed 2 output fields in my first query
SELECT     tmp.ID, tmp.reportsToID, tmp.firstName, tmp.lastName, tmp_1.LastName AS LastNameReport, tmp_1.FirstName AS FirstNameReport
FROM         tmp LEFT OUTER JOIN
                      tmp AS tmp_1 ON tmp.reportsToID = tmp_1.ID

Open in new window

0
 
LVL 10

Expert Comment

by:John Claes
ID: 34917350
You have to do what you have to do ;-)
As long we got your problem solved we're happy ;-)
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34917580
Agree
0
 

Author Closing Comment

by:lrbrister
ID: 34918358
Thanks.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now