T-SQL Challenge #1

This puzzle is part of the TSQL Challenge contest.

Author: Jacob Sebastion


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


  1. 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'


  1. The solution should be a single query that starts with a “SELECT” or “;WITH”


  1. The solution should work on SQL Server 2000 and above.