Solved

Select with heirarchy

Posted on 2011-02-17
9
211 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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