SQL query using rows from another query

Question

I would like to use the results from one query to perform another query and combine the results into a single table.

The first query obtains a set of devices for a particular device version and date

SELECT        
    dbo.DeviceSession.SerialNumber, 
    dbo.DeviceSession.BackupDate
FROM
    dbo.DeviceSession 

SerialNumber	BackupDate
1	2022-01-24
2	2022-02-08
3	2022-02-10

The second query obtains the number of errors for a particular serial number and backup date.

SELECT 
    ErrorCode, COUNT(*) AS [Count]
FROM
    dbo.DeviceSession FULL OUTER JOIN
    dbo.DeviceSessionErrorLogEntry AS DeviceSessionErrorEntry_1 
        ON DeviceSession.Id = DeviceSessionErrorEntry_1.DeviceSessionId
WHERE   
    (dbo.DeviceSession.DeviceSerial LIKE '1') AND  
    (dbo.DeviceSession.BackupDate LIKE '2022-01-24')
GROUP BY ErrorCode

ErrorCode	Count
97	2
150	5

The second query can be combined into a single line by using the "FOR XML PATH('')"

SELECT 
    ErrorCode, COUNT(*) AS [Count]
FROM 
    dbo.DeviceSession 
FULL OUTER JOIN
    dbo.DeviceSessionErrorLogEntry AS DeviceSessionErrorEntry_1 
        ON DeviceSession.Id = DeviceSessionErrorEntry_1.DeviceSessionId
WHERE   
    (dbo.DeviceSession.DeviceSerial LIKE '1') 
    AND (dbo.DeviceSession.BackupDate LIKE '2022-01-24')
GROUP BY ErrorCode
FOR XML PATH('')

XML_FDEWFJEWFOE
<ErrorCode>97</ErrorCode><Count>2</Count><ErrorCode>150</ErrorCode><Count>5</Count>

How would I create a query such that I could obtain a table that lists the total errors for every serial number and backup date from the first query?

Desired output:

SerialNumber	BackupDate	Notes
1	2022-01-24	<ErrorCode>97</ErrorCode><Count>2</Count><ErrorCode>150</ErrorCode><Count>5</Count>
2	2022-02-08	<ErrorCode>97</ErrorCode><Count>5</Count>
3	2022-02-10	<ErrorCode>42</ErrorCode><Count>1</Count>

Answer 1

You can move your error log query into a correlated subquery that references DeviceSession in the outer query. Something like the following:

DECLARE @DeviceSession TABLE (ID INT, SerialNumber INT, BackupDate DATE)
INSERT @DeviceSession
VALUES
    (101, 1, '2022-01-24'),
    (102, 2, '2022-02-08'),
    (103, 3, '2022-02-10')

DECLARE @DeviceSessionErrorLogEntry TABLE (ID INT, DeviceSessionID INT, ErrorCode INT)
INSERT @DeviceSessionErrorLogEntry
VALUES
    (201, 101, 97),
    (202, 101, 150),
    (203, 101, 150),
    (204, 101, 97),
    (205, 101, 150),
    (206, 102, 97),
    (207, 102, 150),
    (208, 102, 150)

SELECT
    DS.SerialNumber,
    DS.BackupDate,
    Notes = (
        SELECT EL.ErrorCode, COUNT(*) AS [Count]
        FROM @DeviceSessionErrorLogEntry EL
        WHERE DS.Id = EL.DeviceSessionId
        GROUP BY EL.ErrorCode
        ORDER BY EL.ErrorCode
        FOR XML PATH(''), TYPE
    )
FROM @DeviceSession DS
ORDER BY DS.SerialNumber

Results:

SerialNumber	BackupDate	Notes
1	2022-01-24	<ErrorCode>97</ErrorCode><Count>2</Count><ErrorCode>150</ErrorCode><Count>3</Count>
2	2022-02-08	<ErrorCode>97</ErrorCode><Count>1</Count><ErrorCode>150</ErrorCode><Count>2</Count>
3	2022-02-10	NULL

The subquery can also be moved to a CROSS APPLY if you would like to separate subquery details from the final select for readability.

If you really need a full outer join (to show error records that have no matching session), the query would need some restructuring with an FULL OUTER JOIN (SELECT DISTINCT DeviceSessionId FROM @DeviceSessionErrorLogEntry EL) IDS ON IDS.DeviceSessionId = DS.Id, and then have the Notes subquery reference those IDs.