TSQL Challenges

TSQL Challenge 1

I would like to invite my readers to participate in a TSQL Challenge. And if it works well, I will come up with more such challenges and we will have more fun solving TSQL problems. I will be sharing some of the interesting TSQL challenges that I see around and we will see different ways to solve them.

Here is the first TSQL Challenge:

I found the following question in one of the SQL Server forums, a few weeks back.

There are three tables with the following data.

Table 1

code        aname
----------- ----------
1           Cat
2           Dog
3           Bird

Table 2

code        bname
----------- ----------
1           aaa
1           bbb
2           ccc
2           ddd

Table 3

code        cname
----------- ----------
1           xxx
1           yyy
1           zzz
2           www

Required Output

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

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)

Please send your answers latest by 5th March 2009. I will announce the winner on 9th March 2009. I look forward to see your queries :-)

Here are the insert scripts for the sample data.

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'