How would you remove the sub-select from this q? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How would you remove the sub-select from this q?

Author

Topic

thebody
Starting Member

4 Posts

Posted - 2009-02-06 : 17:25:13

I have a piece of code running against the default Adventureworks DB:

select * from HumanResources.Employee
where ManagerID = (select EmployeeID from HumanResources.Employee where LoginID = 'adventure-works\david0')

To summarise it finds the Employee ID of the person with LoginID adventure-works\david0' and then returns info on all the employee's that person manages.

I have a homework question that asked for a statement to do this, which the above statement does. But now I need to do it in a single select statement. Any ideas how to do that?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-02-06 : 17:33:17

select e.*
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
where m.LoginID = 'adventure-works\david0'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-06 : 17:40:12

I think you could also do it this way (untested).

select * from HumanResources.Employee
where ManagerID = EmployeeID
AND LoginID = 'adventure-works\david0'

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-02-06 : 17:45:04

That would return 'adventure-works\david0' if he is his own manager

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

thebody
Starting Member

4 Posts

Posted - 2009-02-06 : 17:48:23

Thanks, that's perfect. I suspected the only way that this could be done was with a join to the same table but couldn't figure out how it would work. THis is great.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-06 : 18:16:15

quote:
Originally posted by jhocutt

That would return 'adventure-works\david0' if he is his own manager

Hmm ok I misread the question. I thought he wanted all managers with that login since m.id=e.id.

I'm wondering if this would work (I am away from the DB at the moment so I cant test it).


select *from HumanResources.Employee as a,HumanResources.Employee AS b
where a.ManagerID=b.EmployeeID 
and b.LoginID = 'adventure-works\david0'

Again untested.. just curious if it could be done without the join.

I guess its moot though as the poster already is using your solution to complete his homework.

I'm LearnDing !

r&r

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 18:19:32

quote:
Originally posted by revdnrdy

quote:
Originally posted by jhocutt

That would return 'adventure-works\david0' if he is his own manager

Hmm ok I misread the question. I thought he wanted all managers with that login since m.id=e.id.

I'm wondering if this would work (I am away from the DB at the moment so I cant test it).

Select a.* from HumanResources.Employee as a,HumanResources.Employee AS b
where a.ManagerID=b.EmployeeID
and b.LoginID = 'adventure-works\david0'

Again untested.. just curious if it could be done without the join.

I guess its moot though as the poster already is using your solution to complete his homework.

I'm LearnDing !

r&r

Yes this will also work. You need to avoid old style joins.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:26:45

quote:
Originally posted by revdnrdy

quote:
Originally posted by jhocutt

That would return 'adventure-works\david0' if he is his own manager

Hmm ok I misread the question. I thought he wanted all managers with that login since m.id=e.id.

I'm wondering if this would work (I am away from the DB at the moment so I cant test it).

select *from HumanResources.Employee as a,HumanResources.Employee AS b
where a.ManagerID=b.EmployeeID
and b.LoginID = 'adventure-works\david0'

Again untested.. just curious if it could be done without the join.

I guess its moot though as the poster already is using your solution to complete his homework.

I'm LearnDing !

r&r

this is still old type of join isnt it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources