mardi 24 novembre 2015

How to have multiple where clauses in SQL query

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