title: Joining 3 tables and retrieve all the records from all the tables
body: <p>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.</p>
  
  <p>Table Information</p>
  
  <p><a href="http://img235.imageshack.us/img235/7980/tableinfoow1.png" rel="nofollow noreferrer">alt text http://img235.imageshack.us/img235/7980/tableinfoow1.png</a></p>
  
  <p>(1) If I join tables in TABLE1, TABLE2, TABLE3 sequence I get two rows for record with team B and Level 1.</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>(2) If I join tables in TABLE2, TABLE3, TABLE1 sequence I get correct number of rows in the output.</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>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).</p>
  
  <p>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.</p>