Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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