Merge results of 2 inner queries into single result in SQL

Question

I am trying to merge results of two inner join SQL queries into a single result. This works if both queries return rows; otherwise it returns no rows.

If either of the subqueries doesn't have a record it should be null but the other subquery must show its result. But this query returns empty for both when it has a result for one subquery.

select CASE sovereign.MaxDate
    WHEN null THEN ''
    ELSE sovereign.MaxDate END AS SovereignDate,
    CASE economic.MaxDate
    WHEN null THEN ''
    ELSE economic.MaxDate END AS EconomicDate
from
    (select top 1 Doc_Pub_Date as MaxDate
    from content_ext.dbo.[sovereignrisk] rc
    where rc.Code = 'BJ'
    order by Doc_Pub_Date desc
    ) as sovereign
,
    (select top 1 Doc_Pub_Date as MaxDate
    from content_ext.dbo.[credit_conditions] rc
    where rc.Code = 'BJ'
    order by Doc_Pub_Date desc
    ) as economic

What am I doing wrong?

Answer 1

Here is a short example of how changing the way the sub-queries are used in the entire query will return a result, even if one of the sub-queries does not return a result.

CREATE TABLE #a (val int);
CREATE TABLE #b (val2 int);

INSERT INTO #a VALUES (1);

SELECT * FROM
(
    SELECT TOP 1 val FROM #a
) AS [aa],
(
    SELECT TOP 1 val2 FROM #b
) AS [bb];

SELECT * FROM
(
    SELECT TOP 1 val FROM #a
) AS [aa] FULL OUTER JOIN
(
    SELECT TOP 1 val2 FROM #b
) AS [bb] ON 1=1;

DROP TABLE #a;
DROP TABLE #b;

You can see from the two different resultsets that the query using a FULL OUTER JOIN will always return a result if at least ONE of the sub-queries has a result.