FULL JOIN on severals tables

Question

I have a list of items and, by deposit, stocks, purchase orders, sales orders, forecasts of consumption or production, etc.. I want to set up a query for each item back to me all this information, deposit by deposit. Obviously, it is not said that this information is available for each item.

For example, considering the following tables:

T1
|    REF |
|--------|
|      1 |

T2
|    REF |  DEPOT |
|--------|--------|
|      1 |      A |
|      1 |      B |

T3
|    REF |  DEPOT |
|--------|--------|
|      1 |      A |
|      1 |      C |

T4
|    REF |  DEPOT |
|--------|--------|
|      1 |      B |
|      1 |      C |
|      1 |      D |

If I take only the first three tables (just to start), I have not found better than:

SELECT T1.ref AS T1ref
  , T2.ref AS T2ref, T2.depot AS T2depot
  , T3.ref AS T3ref, T3.depot AS T3depot
FROM T1
  LEFT JOIN T2
    ON T2.ref = T1.ref
  FULL JOIN T3
    ON T3.ref = T1.ref
    AND T3.depot = T2.depot

The output:

|  T1REF |  T2REF | T2DEPOT |  T3REF | T3DEPOT |
|--------|--------|---------|--------|---------|
|      1 |      1 |       A |      1 |       A |
|      1 |      1 |       B | (null) |  (null) |
| (null) | (null) |  (null) |      1 |       C |

What I want:

|  T1REF |  T2REF | T2DEPOT |  T3REF | T3DEPOT |
|--------|--------|---------|--------|---------|
|      1 |      1 |       A |      1 |       A |
|      1 |      1 |       B | (null) |  (null) |
|      1 | (null) |  (null) |      1 |       C |

There must be a clean method to do this, but I didn't find anything. And it's hard to find material on this.

Does anyone have a hint?

The sqlfiddle: http://sqlfiddle.com/#!3/19014/2

Thank you, David.

Edit:
And with T4:

|  T1REF |  T2REF | T2DEPOT |  T3REF | T3DEPOT |  T4REF | T4DEPOT |
|--------|--------|---------|--------|---------|--------|---------|
|      1 |      1 |       A |      1 |       A | (null) |  (null) |
|      1 |      1 |       B | (null) |  (null) |      1 |       B |
|      1 | (null) |  (null) |      1 |       C |      1 |       C |
|      1 | (null) |  (null) | (null) |  (null) |      1 |       D |

I should have named my tables with better names : T1 = items, T2 = stocks, T3 = purchases, T4 = sells. So, T1 will always have all the refs, and also a lot of other information I need.

Answer 1

With you help, I've found that I can obtain almost what I want simply if I forget for a time the items informations (table T1) :

SELECT *
FROM T2
  FULL JOIN T3
    ON T3.ref = T2.ref
    AND T3.depot = T2.depot
  FULL JOIN T4
    ON (
      T4.ref = T2.ref
      AND T4.depot = T2.depot
    )
    OR (
      T4.ref = T3.ref
      AND T4.depot = T3.depot
    )

It gives:

|  T2REF | T2DEPOT |  T3REF | T3DEPOT |  T4REF | T4DEPOT |
|--------|---------|--------|---------|--------|---------|
|      1 |       A |      1 |       A | (null) |  (null) |
|      1 |       B | (null) |  (null) |      1 |       B |
| (null) |  (null) |      1 |       C |      1 |       C |
| (null) |  (null) | (null) |  (null) |      1 |       D |

And I just have to get the T1 info afterward:

SELECT *
FROM T2
  FULL JOIN T3
    ON T3.ref = T2.ref
    AND T3.depot = T2.depot
  FULL JOIN T4
    ON (
      T4.ref = T2.ref
      AND T4.depot = T2.depot
    )
    OR (
      T4.ref = T3.ref
      AND T4.depot = T3.depot
    )
  INNER/RIGHT JOIN T1
    ON T1.ref = COALESCE(T2.ref, T3.ref, T4.ref)

To get:

|  T2REF | T2DEPOT |  T3REF | T3DEPOT |  T4REF | T4DEPOT |  T1REF |
|--------|---------|--------|---------|--------|---------|--------|
|      1 |       A |      1 |       A | (null) |  (null) |      1 |
|      1 |       B | (null) |  (null) |      1 |       B |      1 |
| (null) |  (null) |      1 |       C |      1 |       C |      1 |
| (null) |  (null) | (null) |  (null) |      1 |       D |      1 |

For fun (and better understand the purpose of this request latter), I'm still trying to get this work if I put T1 as the first table...

(Too bad sqlfiddle don't seem to manage several fields with same names...)

Answer 2

An example with all the 4 tables and Coalesce as stated by Gordon Linoff

;with[T1]([REF])as(
       select * from (values(1),(2))[A]([REF])
),
[T2]([REF],[DEPOT])as(
       select*from(values
             (1,'A'),
             (1,'B'),
             (1,'E')
       )[a]([REF],[DEPOT])
),
[T3]([REF],[DEPOT])as(
       select*from(values
             (1,'A'),
             (1,'C')
       )[a]([REF],[DEPOT])
),
[T4]([REF],[DEPOT])as(
       select*from(values
             (1,'B'),
             (1,'C'),
             (1,'D'),
             (1,'E')
       )[a]([REF],[DEPOT])
)

select
*
from [T1]
outer apply (
      select
            T2.REF T2REF,
            T2.DEPOT T2DEPOT,
            T3.REF T3REF,
            T3.DEPOT T3DEPOT,
            T4.REF T4REF,
            T4.DEPOT T4DEPOT
      from T2
      full outer join T3 on
            T2.REF = T3.REF and
            T2.DEPOT = T3.DEPOT
      full outer join T4 on   
            COALESCE(T3.REF,T2.REF) = T4.REF and
            COALESCE(T3.DEPOT,T2.DEPOT) = T4.DEPOT
      where
        COALESCE(T2.REF,T3.REF,T4.REF) = T1.REF
) TR

Answer 3

You can fix your particular query by using coalesce():

SELECT coalesce(T1.ref, t2.ref, t3.ref) AS T1ref

However, instead of using full outer join, I find it easier to start with the lists and combinations that I care about. In this case, you seem to care about t1.ref and depots from all the tables. Perhaps this is closer to what you really want to do:

SELECT t1ref.ref,
       T2.ref AS T2ref, T2.depot AS T2depot,
       T3.ref AS T3ref, T3.depot AS T3depot
FROM (select ref from T1 union
      select ref from T2 union
      select ref from T3
     ) t1ref cross join
     (select depot from T2 union
      select depot from t3
     ) d LEFT JOIN T2
     ON T2.ref = T1ref.ref and
        t2.depot = d.depot LEFT JOIN
     T3
     ON T3.ref = T1ref.ref AND
        T3.depot = d.depot --OR T2.depot IS NULL)