Joining 3 tables and retrieve all the records from all the tables

Question

I am joining three tables (performing a full outer join) so that I can retrieve all the records from all the tables. Problem that I am facing is with the order in which I join tables.

Table Information

alt text http://img235.imageshack.us/img235/7980/tableinfoow1.png

(1) If I join tables in TABLE1, TABLE2, TABLE3 sequence I get two rows for record with team B and Level 1.

SELECT DISTINCT 
    (CASE WHEN T0.[TEAM] IS NOT NULL THEN T0.[TEAM] WHEN T1.[TEAM] IS NOT NULL THEN T1.[TEAM] WHEN T2.[TEAM] IS NOT NULL THEN T2.[TEAM] ELSE T0.[TEAM] END) AS [TEAM], 
    (CASE WHEN T0.[LEVEL] IS NOT NULL THEN T0.[LEVEL] WHEN T1.[LEVEL] IS NOT NULL THEN T1.[LEVEL] WHEN T2.[LEVEL] IS NOT NULL THEN T2.[LEVEL] ELSE T0.[LEVEL] END) AS [LEVEL], 
    T0.[VALUE1] AS [VALUE1], 
    T1.[VALUE2] AS [VALUE2], 
    T2.[VALUE3] AS [VALUE3] 

FROM TABLE1 T0
FULL JOIN TABLE2 T1 ON T0.[TEAM] = T1.[TEAM] AND T0.[LEVEL] = T1.[LEVEL] 
FULL JOIN TABLE3 T2 ON T0.[TEAM] = T2.[TEAM] AND T0.[LEVEL] = T2.[LEVEL]

(2) If I join tables in TABLE2, TABLE3, TABLE1 sequence I get correct number of rows in the output.

SELECT DISTINCT 
    (CASE WHEN T0.[TEAM] IS NOT NULL THEN T0.[TEAM] WHEN T1.[TEAM] IS NOT NULL THEN T1.[TEAM] WHEN T2.[TEAM] IS NOT NULL THEN T2.[TEAM] ELSE T0.[TEAM] END) AS [TEAM], 
    (CASE WHEN T0.[LEVEL] IS NOT NULL THEN T0.[LEVEL] WHEN T1.[LEVEL] IS NOT NULL THEN T1.[LEVEL] WHEN T2.[LEVEL] IS NOT NULL THEN T2.[LEVEL] ELSE T0.[LEVEL] END) AS [LEVEL], 
    T0.[VALUE1] AS [VALUE1], 
    T1.[VALUE2] AS [VALUE2], 
    T2.[VALUE3] AS [VALUE3] 

FROM TABLE2 T0
FULL JOIN TABLE3 T1 ON T0.[TEAM] = T1.[TEAM] AND T0.[LEVEL] = T1.[LEVEL] 
FULL JOIN TABLE1 T2 ON T0.[TEAM] = T2.[TEAM] AND T0.[LEVEL] = T2.[LEVEL]

Problem I am facing is that I am not aware of the input tables and take all these tables as an input from user at runtime and perform a join. I cannot merge two tables at a time since my table can technically merge more than three tables at a time (upto 9 or 10).

How can I ensure that I get all records from all tables (using full outer join) but DO not get two rows as in #1.

Answer 1

If this is what you need:

TEAM LEVEL  Value1  Value2  Value3
A   1        1       NULL    NULL
B   1        NULL    1000    900

Then you can achieve that with the following:

SELECT [TEAM], [LEVEL], MAX(v1) Value1, MAX(v2) Value2, MAX(v3) Value3
FROM (
    SELECT [TEAM], [LEVEL], Value1 v1, NULL v2, NULL v3
    FROM TABLE1
    UNION
    SELECT [TEAM], [LEVEL], NULL, Value2, NULL
    FROM TABLE2
    UNION
    SELECT [TEAM], [LEVEL], NULL, NULL, Value3
    FROM TABLE3
) t0
GROUP BY [TEAM], [LEVEL]

and you can use as many tables as you need.