This puzzle is part of the TSQL Challenge contest.
Author: Jacob Sebastion
Introduction
TSQL Challenge #1 presents a simple but interesting problem. TSQL Challenges are all about solving a given TSQL problem using a SET based query. Most of the times, the task is to format a set of data from one shape to another using a single SET operation. The Challenge in TSQL Challenge #1 is to write a query that takes data from three tables into a given shape.
Sample Data
Here are the three tables.
Table A
code aname
----------- ----------
1 Cat
2 Dog
3 Bird
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.
Expected Results
code aname bname cname
----------- ---------- ---------- ----------
1 Cat aaa xxx
1 Cat bbb yyy
1 Cat NULL zzz
2 Dog ccc www
2 Dog ddd NULL
3 Bird NULL NULL
Rules
- The query should work in SQL Server 2000 as well as SQL Server 2005/2008. However, two separate version of the query is acceptable for SQL Server 2000 and 2005/2008 (SQL Server 2005/8 has some new functions that makes writing this query easier and you can make use of them)
Sample Script
DECLARE @a TABLE (code INT, aname VARCHAR(10))
INSERT INTO @a(code, aname) SELECT 1,'Cat'
INSERT INTO @a(code, aname) SELECT 2,'Dog'
INSERT INTO @a(code, aname) SELECT 3,'Bird'
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'
Restrictions
- The solution should be a single query that starts with a “SELECT” or “;WITH”
Notes
- The solution should work on SQL Server 2000 and above.