In the following query I am trying to get specifically a managers name using the 'not so looked up to' subquery. I am only using this subquery as I do not know how to specify another where clause for the 'ManagerID'
I understand that joins should be used in place of these queries as they operate row by row and not as a set but I don't see another way of doing this. Currently this query will not execute as I get the famous "subquery has returned multiple rows" Any help would be appreciated.
SELECT hrdh.StartDate, hrdh.PositionTitle, HumanResources.Department.Name,
HumanResources.Employee.EmployeeNumber, HumanResources.Employee.Classification, HumanResources.Employee.Status,
HumanResources.Employee.ManagerID AS ManagerID,
Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.SIN,
Person.Contact.DateOfBirth, Person.Contact.PhoneNumber, Person.Contact.EmailAddress, Person.Contact.AddressLine1,
Person.Contact.AddressLine2, Person.Contact.PostalCode, Person.Contact.City, Person.Contact.Province,
(SELECT (Person.Contact.FirstName+ ' ' +Person.Contact.LastName) AS manager
FROM Person.Contact
INNER JOIN HumanResources.Employee
ON Person.Contact.ContactID = HumanResources.Employee.ContactID
WHERE HumanResources.Employee.ManagerID = ManagerID) AS manager
FROM HumanResources.EmployeeDepartmentHistory hrdh, HumanResources.Employee, Person.Contact, HumanResources.Department
WHERE hrdh.EmpID = @empID
Aucun commentaire:
Enregistrer un commentaire