need help finding a Managers name inside a selct that has Mangers ID

[DougP]

The table contains the Persons data and the Enterprise ID of their manager. Can get the Managers name using the txtManagerEnterpriseID at the same time like so
Name ManagerID Manager name
Fred 12345 Sally
Getting Error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

 Declare  @Managername as nvarchar(100)
 Declare  @ManagerEID as nvarchar(20)
 Select txtEnterpriseID ,
		txtSurName + ', ' + txtGivenName as 'Name' ,
		txtManagerEnterpriseID ,
		@ManagerEID = txtManagerEnterpriseID,
		@Managername ,
		txtEmployeeID ,
		(Select txtSurName + ', ' + txtGivenName 
			as 'ManagerName' from   dbo.tblLDAP
			Where txtManagerEnterpriseID = @ManagerEID) ,

		From dbo.tblLDAP

DougP

 

[gmmastros]

Try this:

Select Employee.txtEnterpriseID As EmployeeId,
       Employee.txtSurName + ', ' + Employee.txtGivenName as EmployeeName,
       Employee.txtManagerEnterpriseID As ManagerId,
       Manager.txtSurName + ', ' + Manager.txtGivenName as ManagerName
From   dbo.tblLDAP As Employee
       Left Join dbo.tblLDAP As Manager
	     On Employee.txtManagerEnterpriseID = Manager.txtEnterpriseId

Notice that the same table is referenced twice in the From clause. This is considered a "self join", which really just means you are joining a table to itself. Also notice that I included "As Employee" for one of them, and "As Manager" for the other. When you use a self join, you MUST alias as least one of them so that the query knows which "table" to pull the data from. I chose to alias both of them because I think it makes everything clearer/easier to understand.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

[tt]Getting Error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.[/tt]

I didn't directly answer your question.

There are various ways to use a select statement. For example...

Select Column
From   Table

This will return data as an output recordset.

Then there's this....

Select  @Var1 = Column1,
        @Var2 = Column2
From    Table
Where   ColumnX = 1234

This query will assign values to the variables.

The error message is basically saying that you can use a select to return data OR you can use a select to assign values to variable. You cannot do both at the same time.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Sweet and FAST response. I would get double stars if I could.

wait where is the stars thing? All I see is the RED flag post.

DougP

 

[TamarGranor]

In another forum I use, that button was missing last week and someone said the forum had been accidentally set to private. So maybe the same problem here.

Tamar

 

[gmmastros]

DougP, Your heartfelt thanks is enough for me. Stars are meaningless for me. I do encourage you to use this functionality when others help you though.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

your first post fixed the issue. so here is your second heart felt thanks. "Fooey" on the stars. LOL

DougP

 

[TamarGranor]

George-the main point of the stars isn't to reward the poster, but to help people find good answers.

Tamar