SQL statement to return Null values for multiple columns if no rows found

Question

I have a problem coming up with a way to solve this problem. Namely, I want the second SELECT statement to return null values even if the row is not found using the WHERE clause.

I tried many different solutions, but nothing worked (using IF, CTE, etc.). Can you please help?

    SELECT  RA.id
        ,AllEngagementsAndJobCodes =
        (
            SELECT 
            (
                SELECT
                (...)

                For JSON Path
            ) AS  engagements
            ,
            (
                SELECT [someColumn] as [id], [someColumn2] as [name]
                FROM [someInterface].[someSchema].[someTable]
                WHERE [someID] = '12345'

                For JSON Path, INCLUDE_NULL_VALUES
            ) as jobCodes
        For JSON Path, WITHOUT_ARRAY_WRAPPER
        )

The output of this should be one column (which is trivial for this question) and the second column containing the JSON path, which should look something like this:

    {
        "engagements": [
            {
                //results of the first query, not siginificant for the question
            }
        ],
        "jobCodes": [
            {
                "id": null,
                "name": null
            }
        ]
    }

Note that if the query DOES return something, it might be more than one row and all of them need to be included in the output JSON.

Answer 1

One way to do it is using a common table expression and union all:

;WITH CTEInnerQuery AS
(
    SELECT [someColumn] as [id], [someColumn2] as [name]
    FROM [someInterface].[someSchema].[someTable]
    WHERE [someID] = '12345'
), CTEInnerJson AS
(
    SELECT *
    FROM CTEInnerQuery
    UNION ALL
    SELECT NULL as [id], NULL as [name]
    WHERE NOT EXISTS(SELECT 1 FROM CTEInnerQuery)
)

SELECT  RA.id
    ,AllEngagementsAndJobCodes =
    (
        SELECT 
        (
            SELECT
            (...)

            For JSON Path
        ) AS  engagements
        ,
        (
            SELECT *
            FROM CTEInnerJson
            For JSON Path, INCLUDE_NULL_VALUES
        ) as jobCodes
    For JSON Path, WITHOUT_ARRAY_WRAPPER
    )