sql help pls - 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 2005 Forums

Transact-SQL (2005)

sql help pls

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2008-09-18 : 15:09:16

I have been banging my head against the wall to figure this out - hopefully someone here can help me:

Requirement:
Get branchManager UserName based on @UserName

@UserName = 'John Doe'

tableUsers
UserName BranchNumber EmpNo
John Doe 85 11
Jane Smith 85 12

tableBranches
branchNumber BranchManagerID
85 Jane Smith

Results Wanted: Jane Smith

This is what I came up, but it always returns nothing (no errors, just no records)

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 a.userName = @AssignedTo

If anyone can help, I'd really appreciate it.

Thanks - hope I explained the situation clearly

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-18 : 15:44:44

the variable at the top is @userName but in your WHERE clause it is @AssignedTo
Your tableUsers has a column called EmpNo butin your JOIN criteria you join to [userNumber]

Post the actual, scripted out table structures
and post your actual, complete code.

Be One with the Optimizer
TG

ann
Posting Yak Master

220 Posts

Posted - 2008-09-18 : 15:56:52

Sorry, that was messy of me ....
I was just trying to make my columns easier to read for this purpose, the sp is:

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 a.userName = 'John Doe'

The tables should be:
Users
UserName BranchNumber UserNo
John Doe 85 11
Jane Smith 85 12

Branches
branchNumber BranchManagerID
85 Jane Smith

So here's the odd part, this works fine in sql2000, but it returns nothing in 2005? And yes, I've verified the records exist in both db's

ann
Posting Yak Master

220 Posts

Posted - 2008-09-18 : 16:04:58

Here is the script for the tables:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[branches]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[branches]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO

CREATE TABLE [dbo].[branches] (
[branchNumber] [int] NOT NULL ,
[branchManagerID] [int] NULL

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[userNumber] [int] NOT NULL ,
[userName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branchNumber] [int] NULL

) ON [PRIMARY]
GO

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 a.userName = 'John Doe'

Again - this works in 2000 but not 2005?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-18 : 16:25:11

This ran ok for me on a 2005 box:
although I had to change what you had typed here:

Branches
branchNumber BranchManagerID
85 ~~Jane Smith~~ 12


insert Users
(UserName, BranchNumber, UserNumber)
select 'John Doe', 85, 11 union all
select 'Jane Smith', 85, 12

insert Branches (branchNumber, BranchManagerID )
select 85, 12


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 a.userName = 'John Doe'

results:
Manager
----------------------------------------
Jane Smith

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-18 : 16:32:18

Take your time.... Your query information is still all over the place..

quote:

The tables should be:
Users
UserName BranchNumber UserNo
John Doe 85 11
Jane Smith 85 12

Branches
branchNumber BranchManagerID
85 Jane Smith

Are you sure the "BranchMangerID" is supposed to be Jane Smith and not 12?


drop table #Branches
drop table #Users
CREATE TABLE #branches (
[branchNumber] [int] NOT NULL ,
[branchManagerID] [int] NULL 
) 

CREATE TABLE #users (
[userNumber] [int] NOT NULL ,
[userName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branchNumber] [int] NULL 
)

Insert Into #Branches(BranchNumber,BranchManagerID)
select 85,12 Union all
select 86,14 

Insert into #Users(UserNumber,UserName,BranchNumber)
Select 11,'Jane Doe',85 Union All
Select 12,'Jane Smith',85 Union All
Select 13,'Jane test',86 Union All
Select 14,'Jane Another',86

Select a.UserName,a.UserNumber,a.BranchNumber,c.UserName as BranchManager
from
	#users a
inner Join
	#Branches b
on		a.BranchNumber = b.BranchNumber
Inner Join
   #Users c
on		b.BranchManagerID = c.UserNumber
where a.UserName = 'Jane Doe'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-18 : 16:33:35

Didn't have my screen refreshed, TG already posted the solution..

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources