Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select with heirarchy

Posted on 2011-02-17
9
Medium Priority
?
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 

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 600 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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