CODEWITHSUNDEEP
sqlsql-serverselectstored-procedures
user2571510
user2571510

Reputation: 11377

Combine two small Selects in one in SQL Server

I am using the following nested Selects as part of a larger query.

Is there a way that I can combine both of them in one so that I don't have to run two separate queries?

// ...
R.requester,
(
    SELECT      A.Email AS requesterEmail
    FROM        LogTable A
    WHERE       A.NTID = R.requester
    FOR XML PATH(''), ELEMENTS, TYPE
),
R.approver,
(
    SELECT      B.Email AS approverEmail
    FROM        LogTable B
    WHERE       B.NTID = R.approver
    FOR XML PATH(''), ELEMENTS, TYPE
),
// ...

Upvotes: 1

Views: 39

Answers (2)

christof13
christof13

Reputation: 329

If the queries return the same data you could user UNION ALL 

SELECT      A.Email AS requesterEmail
FROM        LogTable A
WHERE       A.NTID = R.requester
FOR XML PATH(''), ELEMENTS, TYPE

union all

(
   SELECT      B.Email AS approverEmail
   FROM        LogTable B
   WHERE       B.NTID = R.approver
   FOR XML PATH(''), ELEMENTS, TYPE
)

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT A.Email AS requesterEmail,B.Email AS approverEmail
    FROM  LogTable B
JOIN LogTable A
    ON B.NTID = R.approver AND A.NTID = R.requester
    FOR XML PATH(''), ELEMENTS, TYPE

Upvotes: 2

Related Questions