SavantCode SavantCode - 2 months ago 7
SQL Question

Subquery with more then one column

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

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 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');


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


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

Comments