CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
SBB
SBB

Reputation: 8970

TSQL Sub select issue

I have a query that I am working on that seems to be working fine so far. However I wanted to get a little more data from it but I am running into an issue.

Working:

  SELECT A.[employee], 
               sum(A.[awardValue]) as totalAwarded,
               (
                    SELECT B.[supEmpID]
                    FROM empTable as B 
                    WHERE B.[empID] = A.[employee]
               )
        FROM   taxTracker as A
        WHERE  YEAR(A.[awardDate]) = YEAR(GETDATE())
               AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                                FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1))
               AND NOT EXISTS (SELECT employee
                               FROM   taxTrackerTracked
                               WHERE  [year] = YEAR(GETDATE())
                                      AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                                                       FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1)))
        GROUP by employee
        HAVING SUM(A.[awardValue]) > '75.00'

Not Working (Joining data in my sub select):

SELECT A.[employee], 
               sum(A.[awardValue]) as totalAwarded,
               (
                    SELECT B.[supEmpID],
                            C.[Email]
                    FROM empTable as B 
                    JOIN empTable as C
                    ON C.[empID] = B.[supEmpID]
                    WHERE B.[empID] = A.[employee]
               )
        FROM   taxTracker as A
        WHERE  YEAR(A.[awardDate]) = YEAR(GETDATE())
               AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                                FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1))
               AND NOT EXISTS (SELECT employee
                               FROM   taxTrackerTracked
                               WHERE  [year] = YEAR(GETDATE())
                                      AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                                                       FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1)))
        GROUP by employee
        HAVING SUM(A.[awardValue]) > '75.00'

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

Is there an easy fix for this?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

There is an easy fix. In SQL Server the easy way is with cross apply:

    SELECT A.[employee], sum(A.[awardValue]) as totalAwarded,
           bc.*
    FROM   taxTracker as A CROSS APPLY
           (SELECT B.[supEmpID], C.[Email]
                FROM empTable as B 
                JOIN empTable as C
                ON C.[empID] = B.[supEmpID]
                WHERE B.[empID] = A.[employee]
           ) bc
    WHERE  YEAR(A.[awardDate]) = YEAR(GETDATE())
           AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                            FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1))
           AND NOT EXISTS (SELECT employee
                           FROM   taxTrackerTracked
                           WHERE  [year] = YEAR(GETDATE())
                                  AND employee IN (SELECT ParamValues.x1.value('empID[1]', 'VARCHAR(50)')
                                                   FROM   @awardEmployees.nodes('/employees/employee') AS ParamValues(x1)))
    GROUP by employee, bc.supEmpID, bc.Email
    HAVING SUM(A.[awardValue]) > '75.00';

EDIT:

I added the bc column names to the group by. This is a fast fix. In practice, you might want to do the aggregation first as a subquery and then cross apply those results to get the additional columns. I'm leaving the answer as above, because I wanted to make as few changes to the original query as possible.

Upvotes: 2

Related Questions