SQL Server | The Join Puzzle

This question was asked by a Facebook user. In this puzzle you have to join these 3 tables and get the desired output. The catch here is you have to control the number of rows while joining tables . His post says-

 ``` I have 3 tables: TA code aname ----------- ---------- 1 A 2 B 3 C TB code bname ----------- ---------- 1 aaa 1 bbb 2 ccc 2 ddd TC code cname ----------- ---------- 1 xxx 1 yyy 1 zzz 2 www Required O/P :- code aname bname cname ----------- ---------- ---------- ---------- 1 A aaa xxx 1 A bbb yyy 1 A NULL zzz 2 B ccc www 2 B ddd NULL 3 C NULL NULL Please provide solution ```

Please check out the sample input and the expected output below-

Sample Input

–1st table

code aname
1 a
2 b
3 c

–2nd table

code bname
1 aaa
1 bbb
2 ccc
2 ddd

–3rd table

code cname
1 xxx
1 yyy
1 zzz
2 www

Expected Output

Code aname bname cname
1 a aaa xxx
1 a bbb yyy
1 a NULL zzz
2 b ddd www
2 NULL ccc NULL
3 c NULL NULL

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

Script | use below script to create table & insert some sample data

 ``` -- create table TA(code INT,aname varchar(6)); create table TB(code INT,bname varchar(6)); create table TC(code INT,cname varchar(6)); insert into TA values(1,'a'); insert into TA values(2,'b'); insert into TA values(3,'c'); insert into TB values(1,'aaa'); insert into TB values(1,'bbb'); insert into TB values(2,'ccc'); insert into TB values(2,'ddd'); insert into TC values(1,'xxx'); insert into TC values(1,'yyy'); insert into TC values(1,'zzz'); insert into TC values(2,'www'); -- ```

Solution 1-

 ``` -- SELECT ISNULL(p.code,r.code) Code,aname,bname,cname FROM ( SELECT TA.*,cName, ROW_NUMBER() OVER (Order BY Ta.Code) rnk FROM TA LEFT JOIN TC ON TA.Code = Tc.Code )p FULL OUTER JOIN ( SELECT *,ROW_NUMBER() OVER (Order BY Code) rnk FROM TB )r ON r.code = p.code AND r.rnk = p.rnk ORDER BY ISNULL(p.code,r.code) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com