# Joining 3 tables and retrieve all of the records all the tables

I'm joining three tables (carrying out a complete outer join) to ensure that I'm able to retrieve all of the records all the tables. Problem that i'm facing is by using an order by which I join tables.

Table Information

(1) Basically join tables in TABLE1, TABLE2, TABLE3 sequence I recieve 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) Basically join tables in TABLE2, TABLE3, TABLE1 sequence I recieve correct quantity of rows within 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'm facing is that i'm unaware of the input tables and take each one of these tables being an input from user at runtime and execute a join. I am unable to merge two tables at any given time since my table can technically merge a lot more than three tables at any given time (up to 9 or 10).

How do i make sure that I recieve all records all tables (using full outer join) but don't get two rows as with #1.

If this sounds like the thing you need:

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

You'll be able to make that happen using 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 will use as numerous tables since you need.

That's the phrase a complete OUTER JOIN (which, when used, is nearly almost always a sign of the poor design - I personally use a complete OUTER JOIN about annually).

Possibly were you to give the outcomes you had been searching for?

I am thinking UNION, GROUP BY and COALESCE.

The thing you need would be to add one more match condition around the second join expression to let it match the 2nd table's team/level values. I have also simplified theyOrdegree 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 did not show on a single row as T2 since you never permitted that like a match possibility.