CODEWITHSUNDEEP
sql-servert-sql
b00kgrrl
b00kgrrl

Reputation: 597

T-SQL outer join with multiple tables not working

I'm trying to write a query in the MS SQL Server tool, although I'm more used to Oracle SQL Developer. The query below runs, but it only returns results from the first table. I've also tried using a LEFT JOIN instead of a FULL OUTER JOIN, but I get the same results. What am I doing wrong?

SELECT c_s.FiscalYear, c_s.LeadMinistry, c_s.LogNo, c_s.MinRef, c_s.nl_Form
  FROM [cds].[dbo].[c_Submission] c_s
FULL OUTER JOIN [cds].[dbo].[c_AdminData] c_ad
ON c_s.LogNo = c_ad.LogNo
FULL OUTER JOIN [cds].[dbo].[c_Edited Key Comments] c_kc
ON c_s.LogNo = c_kc.LogNo
FULL OUTER JOIN [cds].[dbo].[c_Edited Minutes] c_min
ON c_s.LogNo = c_min.LogNo
FULL OUTER JOIN [cds].[dbo].[c_MB20BA] c_bn
ON c_s.LogNo = c_bn.LogNo
WHERE (c_s.LogNo IS NOT NULL)
ORDER BY c_s.LogNo;

Upvotes: 0

Views: 74

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Condition in the Where should be in the ON clause otherwise the full join will turn into one sided join:

select c_s.FiscalYear,
    c_s.LeadMinistry,
    c_s.LogNo,      -- Use coalesce to get non null LogNo from the join
    c_s.MinRef,
    c_s.nl_Form
from [cds].[dbo].[c_Submission] c_s
full outer join [cds].[dbo].[c_AdminData] c_ad
    on c_s.LogNo = c_ad.LogNo
full outer join [cds].[dbo].[c_Edited Key Comments] c_kc
    on c_s.LogNo = c_kc.LogNo
full outer join [cds].[dbo].[c_Edited Minutes] c_min
    on c_s.LogNo = c_min.LogNo
full outer join [cds].[dbo].[c_MB20BA] c_bn
    on c_s.LogNo = c_bn.LogNo
        and c_s.LogNo is not null
order by c_s.LogNo

You can use coalesce to get non null LogNo:

COALESCE(c_s.LogNo, c_ad.LogNo, c_kc.LogNo, c_min.LogNo)

Upvotes: 1

Alexander Sharovarov
Alexander Sharovarov

Reputation: 311

If you want to pre-filter the first table it is better to do that in sub-query or CTE. And if you want true full join of all tables you need to use COALESCE on the Keys. Otherwise it becomes a LEFT join.

;WITH _Submission AS 
(
    SELECT * FROM  [cds].[dbo].[c_Submission] c_s
    WHERE (c_s.LogNo IS NOT NULL)
)

SELECT c_s.FiscalYear, c_s.LeadMinistry,  COALESCE(c_s.LogNo, c_ad.LogNo, c_kc.LogNo, c_min.LogNo) AS LogNo, c_s.MinRef, c_s.nl_Form
FROM _Submission c_s
    FULL OUTER JOIN [cds].[dbo].[c_AdminData] c_ad
        ON c_s.LogNo = c_ad.LogNo
    FULL OUTER JOIN [cds].[dbo].[c_Edited Key Comments] c_kc
        ON COALESCE(c_s.LogNo, c_ad.LogNo)  = c_kc.LogNo
    FULL OUTER JOIN [cds].[dbo].[c_Edited Minutes] c_min
        ON COALESCE(c_s.LogNo, c_ad.LogNo, c_kc.LogNo) = c_min.LogNo
    FULL OUTER JOIN [cds].[dbo].[c_MB20BA] c_bn
        ON  COALESCE(c_s.LogNo, c_ad.LogNo, c_kc.LogNo, c_min.LogNo)  = c_bn.LogNo
ORDER BY LogNo;

Upvotes: 2

Related Questions