• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Select with heirarchy

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
lrbrister
Asked:
lrbrister
  • 4
  • 3
  • 2
2 Solutions
 
kemi67Commented:
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
 
John ClaesCommented:

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
 
John ClaesCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
John ClaesCommented:
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
 
lrbristerAuthor Commented:
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
 
kemi67Commented:
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
 
John ClaesCommented:
You have to do what you have to do ;-)
As long we got your problem solved we're happy ;-)
0
 
kemi67Commented:
Agree
0
 
lrbristerAuthor Commented:
Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now