question

ashok2012 avatar image
ashok2012 asked

How to achieve this?

Table A: code aname ----------- ---------- 1 A 2 B 3 C Table B: code bname ----------- ---------- 1 aaa 1 bbb 2 ccc 2 ddd Table C code cname ----------- ---------- 1 xxx 1 yyy 1 zzz 2 www The task is to write a query that produces the following output from the above tables. Output: +------------------------+ | 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 | +------------------------+ Script: DECLARE @a TABLE (code INT, aname VARCHAR(10)) INSERT INTO @a(code, aname) SELECT 1,'A' INSERT INTO @a(code, aname) SELECT 2,'B' INSERT INTO @a(code, aname) SELECT 3,'C' DECLARE @b TABLE (code INT, bname VARCHAR(10)) INSERT INTO @b(code, bname) SELECT 1,'aaa' INSERT INTO @b(code, bname) SELECT 1,'bbb' INSERT INTO @b(code, bname) SELECT 2,'ccc' INSERT INTO @b(code, bname) SELECT 2,'ddd' DECLARE @c TABLE (code INT, cname VARCHAR(10)) INSERT INTO @c(code, cname) SELECT 1,'xxx' INSERT INTO @c(code, cname) SELECT 1,'yyy' INSERT INTO @c(code, cname) SELECT 1,'zzz' INSERT INTO @c(code, cname) SELECT 2,'www' SELECT * FROM @a SELECT * FROM @b SELECT * FROM @c
t-sqlquestion-list
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ashok2012 avatar image ashok2012 commented ·
Any one interested to give me answer?
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
select a.code, a.aname, newb.bname, newc.cname from ( SELECT b.code as bcode, b.bname, b_rank = dense_rank()over(partition by b.code order by b.bname) FROM @b b )newb full join ( SELECT c.code as ccode, c.cname, c_rank = dense_rank()over(partition by c.code order by c.cname) FROM @c c ) newc on newb.b_rank = newc.c_rank and newb.bcode = newc.ccode right join @a a on a.code = isnull(newb.bcode, newc.ccode)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ashok2012 avatar image ashok2012 commented ·
Kev Riley Thanks for your Answer you are great!!!!!!!!!!!!!!!!!!!
0 Likes 0 ·
yaj avatar image
yaj answered
Is there a way using CTE or simply without using ranking functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.