Simple (I think) self join help needed

[Moebius01]

Ok, it may be just a bad day and my brain is offline, but this seems like it should be pretty simple, yet I'm stuck.

Table contains EmployeeID and SupervisorID.

I need a query to list each EmployeeID, then whether or not they also appear as a Supervisor. I've tried a dozen different ways to get to this, but I keep getting incorrect results.

Can someone put me out of my misery?

 

[kaht]

Here's a small example:

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (employee [COLOR=blue]varchar[/color](10), supervisor [COLOR=blue]varchar[/color](10))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a
[COLOR=blue]select[/color] [COLOR=red]'bob'[/color], [COLOR=red]'steve'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'steve'[/color], [COLOR=red]'harry'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'diane'[/color], [COLOR=red]'susan'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'susan'[/color], [COLOR=red]'meredith'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'meredith'[/color], [COLOR=red]'uncle rico'[/color]

[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] x.employee, [COLOR=blue]case[/color] [COLOR=blue]when[/color] (y.supervisor [COLOR=blue]is[/color] null) [COLOR=blue]then[/color] 0 [COLOR=blue]else[/color] 1 [COLOR=blue]end[/color] isSupervisor
[COLOR=blue]from[/color] @a x
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] @a y
   [COLOR=blue]on[/color] x.employee = y.supervisor

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[SQLSister]

yep a self join would be the way to go. something like:

select e1.employeid, 
case when e2.employeeid  is null then null else 'supervisor' end 
from employee e1
left join employee e2 on e1.employee1 = e2.supervisorId

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

My advice is very similar to kaht's. The weird part is... the Distinct was bugging me. Of course, it's the right thing to do, but it was bugging me.

So, I thought about this a little, and decided to write a query that counts the number of subordinates. The query is...

Declare @Temp Table(EmployeeId int, SupervisorId Int, Name VarChar(20))

Insert Into @Temp Values(1, null, 'Fred')
Insert Into @Temp Values(2, 1, 'Barney')
Insert Into @Temp Values(3, 2, 'Wilma')
Insert Into @Temp Values(4, 2, 'BamBam')

Select A.EmployeeId, Count(B.SupervisorId) IsSupervisor
From   @Temp A
       Left Join @Temp B
         On A.EmployeeId = B.SupervisorID
Group By A.EmployeeId

Of Course, this isn't exactly what you were asking for, but you can throw a convert on there to get it. Like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](EmployeeId [COLOR=blue]int[/color], SupervisorId [COLOR=blue]Int[/color], [COLOR=blue]Name[/color] [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, null, [COLOR=red]'Fred'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, 1, [COLOR=red]'Barney'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, 2, [COLOR=red]'Wilma'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4, 2, [COLOR=red]'BamBam'[/color])

[COLOR=blue]Select[/color] A.EmployeeId, [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Bit[/color], [COLOR=#FF00FF]Count[/color](B.SupervisorId)) IsSupervisor
[COLOR=blue]From[/color]   @Temp A
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] @Temp B
         [COLOR=blue]On[/color] A.EmployeeId = B.SupervisorID
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] A.EmployeeId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cmmrfrds]

Or, if you have sql server 2005 you can use a recursive query using the With.

see for example.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm

 

[kaht]

George, how come Betty, Pebbles, and Dino get no representation?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[gmmastros]

I couldn't remember their names.

I can remember (relatively) obscure T-SQL functions (like ParseName, Binary_Checksum, etc....) but I can't remember names. But thanks for asking, Lisa.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

I think you got me confused with someone else, my name is Bart

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]