Need help with SQL select within Select Find manager name using a person manager ID number

[leo57]

I want to use a one liner to get the Managers name along with the employees name. the Managers ID is txtManagerEnterpriseID
this T-SQL returns a NULL for Manager name right now.
can someone help me figure this out?

Select txtEnterpriseID,txtGivenname, txtSurname, txtCity ,txtManagerEnterpriseID ,
([highlight #FCE94F]Select (txtGivenname + ' ' + txtSurname)  from VLDAP Where
txtEnterpriseID =txtManagerEnterpriseID) AS Managername[/highlight]
from VLDAP
Where txtGivenname like 'st%'
And txtSurname like 'Cornell'

TIA

 

[gmmastros]

Try this:

Select txtEnterpriseID,
       txtGivenname, 
	   txtSurname, 
	   txtCity ,
	   txtManagerEnterpriseID ,
       (
	      Select (txtGivenname + ' ' + txtSurname)  
		  from   VLDAP As Employee
		  Where  Employee.txtEnterpriseID =txtManagerEnterpriseID
       ) AS Managername
from   VLDAP
Where  txtGivenname like 'st%'
       And txtSurname like 'Cornell'

If this works for you, and you want me to explain it, please let me know.

-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

 

[JohnDuncanTB]

If either txtGivenname or txtSurname are NULL, then the result is NULL. Try something like ...

Select txtEnterpriseID,txtGivenname, txtSurname, txtCity ,txtManagerEnterpriseID ,
(Select (IsNull(txtGivenname + ' ','') + IsNull(txtSurname,''))  from VLDAP Where
txtEnterpriseID =txtManagerEnterpriseID) AS Managername
from VLDAP
Where txtGivenname like 'st%'
And txtSurname like 'Cornell'

--JD
"Behold! As a wild ass in the desert go forth I to my work."