Solved

Select with heirarchy

Posted on 2011-02-17
9
206 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

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.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

16 Experts available now in Live!

Get 1:1 Help Now