SavantCode SavantCode - 2 months ago 7
SQL Question

Subquery with more than one column

I try to build a subquery with more than one column. Like this:

--SELF JOIN:
WITH Employees AS
(
SELECT
e.EmployeeID, e.ManagerID, e.Title,
c.FirstName + ISNULL(' ' + c.MiddleName,'') + ' ' + c.LastName AS EmpName
FROM
Employee AS e
INNER JOIN
Contact AS c ON e.ContactID = c.ContactID
)
SELECT
emp.EmployeeID, emp.ManagerID, emp.EmpName, emp.Title AS EmpTitle,
mgr.EmpName as MgrName, mgr.Title as MgrTitle
FROM
Employees AS Emp
INNER JOIN
Employees AS Mgr ON Emp.ManagerID = Mgr.EmployeeID;

--2
WITH Employees AS
(
SELECT
e.EmployeeID, e.ManagerID, e.Title,
c.FirstName + ISNULL(' ' + c.MiddleName,'') + ' ' + c.LastName AS EmpName
FROM
Employee AS e
INNER JOIN
Contact AS c ON e.ContactID = c.ContactID
)
SELECT
EmployeeID, ManagerID, EmpName, Title
FROM
Employees
WHERE
EmployeeID IN (SELECT EmployeeID, er2.MaritalStatus
FROM Employees AS e
INNER JOIN AdventureWorks2012.HumanResources.Employee AS er2 ON e.ManagerID = er2.BusinessEntityID
WHERE er2.MaritalStatus = 'M');


I want also to show the
MarialStatus
. But I can't do it this way, because I get the error:


Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.


But If I can't do it with exists in the subquery. So my question is: what is the proper way that I can select more then one column in the subquery?

Thank you

Answer

You can't return 2 values in a sub query.So you will have to remove the MaritalStatus column. That's the problem with your query.

USE tempdb;
WITH
Employees AS(
SELECT e.EmployeeID, e.ManagerID,e.Title,
c.FirstName + ISNULL(' ' + c.MiddleName,'') + ' ' + c.LastName AS EmpName
FROM Employee AS e
INNER JOIN Contact AS c
ON e.ContactID = c.ContactID
)
SELECT emp.EmployeeID, emp.ManagerID, emp.EmpName, emp.Title AS EmpTitle,
mgr.EmpName as MgrName, mgr.Title as MgrTitle
FROM Employees AS Emp INNER JOIN Employees AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID;
--2
WITH Employees AS (
SELECT e.EmployeeID, e.ManagerID,e.Title,
c.FirstName + ISNULL(' ' + c.MiddleName,'') + ' ' + c.LastName AS EmpName
FROM Employee AS e
INNER JOIN Contact AS c
ON e.ContactID = c.ContactID)

SELECT e.EmployeeID, e.ManagerID, e.EmpName, e.Title,er2.MaritalStatus
FROM Employees e
INNER JOIN AdventureWorks2012.HumanResources.Employee AS er2 
ON e.ManagerID = er2.BusinessEntityID
WHERE er2.MaritalStatus = 'M');

I don't think you need a subquery there,because INNER JOIN will give you only records if there's a match. So do a INNER JOIN fro the table and put the MaritalStatus column in the select clause