SQL Challange

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. The query should run on SQL Server 2000 as well as on SQL Server 2005.

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

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'

Write a Single Query that should run on SQL Server 2000 and SQL Server 2005