SBB
SBB

Reputation: 8970

TSQL use inner select result for outer FROM

I have a query that looks like the following:

    A.[approvalDate],
    A.[locationID],
    B.[FirstName] + ' ' + B.[LastName] AS nomineeName,
    B.[ntid] AS nomineeNTID,
    B.[qid] AS nomineeQID,
    B.[GeoLocationDesc] as locationName,
    C.[FirstName] + ' ' + C.[LastName] AS submitName,
    C.[ntid] AS submitNTID,
    C.[empID] submitEmpID,
    D.[categoryName],
    (
        SELECT G.[qid] FROM empowermentProxies AS G WHERE G.[proxy] = @QID
    ),
    E.[FirstName] + ' ' + E.[LastName] as behalfOf,
    E.[NTID] AS behalfOfNTID
FROM   empowermentSubmissions AS A
    INNER JOIN
    empTable AS B
    ON A.[nomineeQID] = B.[qid]
    LEFT OUTER JOIN
    empTable AS C
    ON A.[subQID] = C.[qid]
    INNER JOIN
    empowermentCategories AS D
    ON A.[categoryID] = D.[catID]
    JOIN empTable AS E
    ON E.[qid] = G.[qid]
WHERE  @QID IN (SELECT proxy FROM empowermentProxies)
    AND A.[statusID] = 1
FOR        XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');

I am trying to user the result of the inner select G.qid in the join statement but keep getting an error saying G isn't bound.

Is this possible to do? There should only be 1 result returned from the inner select.

Upvotes: 0

Views: 40

Answers (2)

Adi
Adi

Reputation: 232

please try below one

 A.[approvalDate],
                                   A.[locationID],
                                   B.[FirstName] + ' ' + B.[LastName] AS nomineeName,
                                   B.[ntid] AS nomineeNTID,
                                   B.[qid] AS nomineeQID,
                                   B.[GeoLocationDesc] as locationName,
                                   C.[FirstName] + ' ' + C.[LastName] AS submitName,
                                   C.[ntid] AS submitNTID,
                                   C.[empID] submitEmpID,
                                   D.[categoryName],
                                   sol1.qid,
                                   E.[FirstName] + ' ' + E.[LastName] as behalfOf,
                                   E.[NTID] AS behalfOfNTID
                            FROM   empowermentSubmissions AS A
                                   INNER JOIN
                                   empTable AS B
                                   ON A.[nomineeQID] = B.[qid]
                                   LEFT OUTER JOIN
                                   empTable AS C
                                   ON A.[subQID] = C.[qid]
                                   INNER JOIN
                                   empowermentCategories AS D
                                   ON A.[categoryID] = D.[catID]
                                   JOIN empTable AS E
                                   ON E.[qid] = G.[qid]
    left join (SELECT G1.[qid] FROM empowermentProxies AS G1 WHERE G.[proxy] = @QID) sol1
    on g.qid=sol1.qid



                            WHERE  @QID IN (SELECT proxy FROM empowermentProxies)
                                   AND A.[statusID] = 1
                            FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Just move it to the from clause:

    . . .
    D.[categoryName],
    Q.g_qid,
    . . .
FROM   empowermentSubmissions A INNER JOIN
       empTable B
       ON A.[nomineeQID] = B.[qid] LEFT OUTER JOIN
       empTable C
       ON A.[subQID] = C.[qid] INNER JOIN
       empowermentCategories D
       ON A.[categoryID] = D.[catID] CROSS JOIN
       (SELECT G.[qid] as g_qid
        FROM empowermentProxies G
        WHERE G.[proxy] = @QID
       ) G JOIN
       empTable E
       ON E.[qid] = G.[qid]

Upvotes: 2

Related Questions