SQL Server joining multiple CTE

Question

I have 4 Common Table Expressions each contains 2 columns (RowNumber, AccountNumber) but contain variable records in each CTE depending upon the query parameters. Purpose is to keep all non null account numbers at the top for each CTE after joining.

I am joining 4 CTE's using FULL Join on the basis of RowNumber. The problem I am getting is the sequence of AccountNumber is not continuous i.e. it includes some null values in between Accountnumber in some cases. I want to keep all non null values always combined and at the top with nulls. The number of AccountNumber's in each CTE are always different.

SELECT
  ISNULL(Cte_FirstYear.AccountNumber,'') as FirstYear,
  ISNULL(Cte_SecondYear.AccountNumber,'') as SecondYear,
  ISNULL(cte_ThirdYear.AccountNumber,'') as ThirdYear,
  ISNULL(cte_FourthYear.AccountNumber,'') as FourthYear
FROM cte_ThirdYear
FULL OUTER JOIN
cte_FirstYear
on 
cte_ThirdYear.RowNumber=cte_FirstYear.RowNumber
full outer join  Cte_SecondYear
on 
cte_ThirdYear.RowNumber=Cte_SecondYear.RowNumber
full outer join  cte_FourthYear
on 
cte_ThirdYear.RowNumber=cte_FourthYear.RowNumber

Here is how I am getting the output;

FirstYear SecondYear ThirdYear FourthYear
1           2           3         4
5           6           7         1
            9          NULL      NULL
                       NULL
                        9         9
                        10       NULL

Here is expected output;

FirstYear SecondYear ThirdYear FourthYear
1           2           3         4
5           6           7         1
            9           9         9
                        10

"At top" - nope. Rows in a resultset have no inherent order unless the query that generated them included the appropriate order by clause. You have no such clause, so rows can appear in any order. In addition, you are using full outer joins - which means you intended and expected null values from the various tables where there are no matches. Absent a script that can be used to examine your schema and your query, it will be difficult for anyone to provide useful suggestions. — SMor, May 18 '19 at 00:53
And it certainly does not help (or inspire confidence) that your query produces columns named '1st Year', 'Second Year', etc. while your results (actual and desired) have columns named 'FirstYear', 'SecondYear', etc. And how can you debug your code when your resultset contains nothing but AccountNumber yet you join on RowNumber? — SMor, May 18 '19 at 00:55
ideally you did not get null value based on your on condition. Use cross join instead of full outer join. still you have issue than, post your sample data of each cte. — Ajay2707, May 18 '19 at 05:16
in your current query you can add this line to ignore null values in eachon condition : on cte_ThirdYear.RowNumber=cte_FirstYear.RowNumber and on cte_ThirdYear.RowNumber is not null and cte_FirstYear.RowNumber is not null — Ajay2707, May 18 '19 at 05:21
Thanks for help @SMor yes it was order by issue. Also I did some modification to the query and used left join instead. — Amardeep Sharma, May 18 '19 at 07:50

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Based on the explained by @Donnie in the link

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.

You can add this line to ignore :
on cte_ThirdYear.RowNumber=cte_FirstYear.RowNumber and on cte_ThirdYear.RowNumber is not null and cte_FirstYear.RowNumber is not null

Read this pdf : http://stevestedman.com/wp-content/uploads/TSqlJoinTypePoster1.pdf

Amardeep Sharma · Answer 2 · 2019-05-18T20:54:58.217

I created another CTE which takes the maximum records from 4 ctes and generates RowNumber 1 to N (Max. Number of records in 4 CTE's) and joined all 4 CTE's with it using LEFT JOIN. Here is how I modified the query to achieve the result;

Cte_Max(RowNumber) AS (
SELECT  TOP 
(
select max(c)  from 
(
select count(*) c from cte_FirstYear
UNION
select count(*) c from Cte_SecondYear
UNION
select count(*) c from cte_ThirdYear
UNION
select count(*) c from cte_FourthYear
) x
)
ROW_NUMBER() OVER (ORDER BY c1.id asc) as  RowNumber
FROM          syscolumns AS c1
CROSS JOIN    syscolumns AS c2
)

select 
ISNULL(cte_FirstYear.AccountNumber,'') as  FirstYear,
ISNULL(Cte_SecondYear.AccountNumber,'') as SecondYear,
ISNULL(cte_ThirdYear.AccountNumber,'') as ThirdYear,
ISNULL(cte_FourthYear.AccountNumber,'') as FourthYear
from Cte_Max
LEFT join cte_FirstYear
on
Cte_Max.RowNumber=cte_FirstYear.RowNumber
LEFT join 
Cte_SecondYear
on 
Cte_Max.RowNumber=Cte_SecondYear.RowNumber
LEFT join 
cte_ThirdYear
on
Cte_Max.RowNumber =cte_ThirdYear.RowNumber
LEFT join 
cte_FourthYear
on
Cte_Max.RowNumber =cte_FourthYear.RowNumber
ORDER BY Cte_Max.RowNumber

SQL Server joining multiple CTE

2 Answers2