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?
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.