A better way - SQL Server Forums

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

Transact-SQL (2000)

A better way

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2006-07-31 : 15:24:30

I have to retreive a branchmanager name from a table. In order to do this I go through this:

Table1 users

userNumber (int), username(varchar), branchNumber(int)....

table2 branches

branchNumber(int), branchName (varchar), branchManger (int)...

1 - get the branchNumber for user from users table

Select branchNumber
From users
Where userName = 'Ann LastName'

2 - get the branchmangerID from branches (this is the userNumber on the users table)

SELECT BranchManagerID
FROM Branches

Where BranchNumber = branchNumberResult from #1

3.
Select userName
From users
Where userNumber = branchNumberResult from #2

Is there a way to do combine these select statements?
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-07-31 : 15:51:37

Select Manager = C.userName
From users A
Inner Join Branches B
On A.BranchNumber = B.BranchNumber
Inner Join users C
On B.BranchManagerID = C.userNumber
Where userName = 'Ann LastName'

Corey