Using IN operator in Subquery with Group By

Question

I'm having some trouble with a query. I'm supposed to pull up all the client info where the client has only taken 1 test and passed. I was told to use the IN operator. This is what I have:

SELECT *
  FROM Client
 WHERE ClientName IN (SELECT ClientName, COUNT(TestNbr)
                        FROM Test
                    GROUP BY ClientName, TestResult
                      HAVING COUNT(TestNbr)=1
                         AND TestResult='Pass');

I get this error:

(SELECT ClientName, COUNT(TestNbr)
 *
ERROR at line 4: 
ORA-00913: too many values

I understand that it's because I shouldn't have any other entry except for ClientName in that line. How can I fix this problem?

Answer 1

remove the COUNT(*) column on the subquery as it is not neccesary on the result,

SELECT  *
FROM    Client
WHERE   ClientName IN
        (
            SELECT  ClientName
            FROM    Test
            GROUP   BY ClientName, TestResult
            HAVING  COUNT(TestNbr) = 1 AND TestResult='Pass'
        );

but I'd rather use JOIN instead of using IN

SELECT  DISTINCT a.*
FROM    Client a
        INNER JOIN
        (
            SELECT  ClientName
            FROM    Test
            GROUP   BY ClientName, TestResult
            HAVING  COUNT(TestNbr) = 1 AND TestResult='Pass'
        ) b ON a.ClientName = b.ClientName

for faster performance, add an index on column ClientName on both tables.

Answer 2

An IN clause can only contain one column if a sub-query is used. You should remove the COUNT from the columns selected if it's a problem.

What you might want to do is make a VIEW that represents the sub-query and then JOIN against that.