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.

I don't have time to generate a query for this at the moment, but perhaps you should *really* rethink your database structure. — Ry Biesemeyer, Jan 14 '09 at 04:51
I am writing an ETL application which allows user to merge files and hence I need to do this operation. For sure this is not the part of database design of my application :-) — MOZILLA, Jan 14 '09 at 06:01

score 3 · Answer 1 · answered Jan 14 '09 at 12:50

3

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.

answered Jan 14 '09 at 12:50

Recep

18,991
2
28
21

Union is suggested as a general alternative to FULL OUTER JOIN here: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx However, if you must use full outer join, I think you have to use nested subqueries so you are joining only two results sets at a time like this: SELECT * FROM (t1 FULL JOIN t2 ON (t1.c1 = t2.c1)) FULL JOIN t3 ON (t1.c1 = t3.c1); – Triynko Aug 15 '11 at 17:51

Hafthor · Answer 2 · 2009-09-24T21:16:22.560

What you need is to add an additional match condition on the second join expression to allow it to match the second table's team/level values. I've also simplified the team/level column expressions using ISNULL:

SELECT DISTINCT 
    ISNULL(T0.[TEAM],ISNULL(T1.[TEAM],T2.[TEAM])) AS [TEAM],
    ISNULL(T0.[LEVEL],ISNULL(T1.[LEVEL],T2.[LEVEL])) AS [LEVEL],
    T0.[VALUE1], T1.[VALUE2], T2.[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])
                    OR (T1.[TEAM] = T2.[TEAM] AND T1.[LEVEL] = T2.[LEVEL])

See? T1 didn't show on the same row as T2 because you never allowed that as a match possibility.

score 0 · Answer 3 · edited Mar 20 '13 at 12:25

AS u define in yru query distinct that means there r multiple entry along one in this union all is the best to use because there are duplication in the data.

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

Cade Roux · Answer 4 · 2009-01-14T05:10:19.240

0

That's the definition of a FULL OUTER JOIN (which, when used, is almost invariably an indication of a poor design - I use a FULL OUTER JOIN about once a year).

Perhaps if you gave the results you were looking for?

I'm thinking UNION, GROUP BY and COALESCE.

edited Jan 14 '09 at 05:10

answered Jan 14 '09 at 04:54

Cade Roux

88,164
40
182
265

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

4 Answers4

Linked