Can I use more than one column in subquery?

sql, subquery

Solution

Not in an `IN` clause (or at least not the way you are trying to use it. Some RDBMSs allow tuples with more than one column in the `IN` clause but it wouldn't help your case here)

You just need to remove the `COUNT(*)` from the `SELECT` list to achieve your desired result.

SELECT NAME, ID 
FROM EMPLOYEES
WHERE ID IN
    ( 
    SELECT PersonID
    FROM PROJECTS
    GROUP BY PersonID
    HAVING COUNT(*) > 3
    )

If you wanted to also return the count you could join onto a derived table or common table expression with more than one column though.

SELECT E.NAME,
       E.ID,
       P.Cnt
FROM   EMPLOYEES E
       JOIN (SELECT PersonID,
                    Count(*) AS Cnt
             FROM   PROJECTS
             GROUP  BY PersonID
             HAVING Count(*) > 3) P
         ON E.ID = P.PersonID

Problem

I want to show the names of all employees from the `EMPLOYEES` table who are working on more than three projects from the `PROJECT` table. `PROJECTS.PersonID` is a a foreign key referencing `EMPLOYEES.ID`: ``` SELECT NAME, ID FROM EMPLOYEES WHERE ID IN ( SELECT PersonID, COUNT(*) FROM PROJECTS GROUP BY PersonID HAVING COUNT(*) > 3 ) ``` Can I have both `PersonID`, `COUNT(*)` in that subquery, or there must be only one column?

Original source