0

I have three tables, we can call them "t1", "t2" and "t3", that I need to join. I want to match them over two attributes, lets call them "Attr1" and "Attr2", and keep all the tuples in all three tables wther they match or not. There are tuples in all tables that have no match in the others (majority) and there are a few tuples that have a match in one or the other of the other tables and there are a minority (around 1% of all the tuples in all three tables) of the tuples that have a match in every table.

I have tried the solution below:

select Attr1 = case 
when a.Attr1 is null and b.Attr1 is null then c.Attr1
when a.Attr1 is null and c.Attr1 is null then b.Attr1
when c.Attr1 is null and b.Attr1 is null then a.Attr1
when a.Attr1 is null and b.Attr1 is not null and c.Attr1 is not null then c.Attr1
when c.Attr1 is null and b.Attr1 is not null and a.Attr1 is not null then b.Attr1
when b.Attr1 is null and b.Attr1 is not null and c.Attr1 is not null then a.Attr1
else a.Attr1
end, Attr2 = /*Same principle for Attr2 as used for Attr1*/, other variables
from t1 a
full outer join t2 b on a.Attr1 = b.Attr1 and a.Attr2 = b.Attr2
full outer join t3 c on (a.Attr1 = c.Attr1 and a.Attr2 = c.Attr2) and (b.Attr1 = c.Attr1 and b.Attr2 = c.Attr2)

The script runs without errors but the last join doesn't work. It seems to failure on the match between t3 and t2. It works on the match between t3 and t1.

I've also tried the "isnull" solution suggested by Serge at Multiple FULL OUTER JOIN on multiple tables but I could not get that to work eather. There I guess that I didn't know how to use multiple attributes to the join correctly.

One solution I've thought about is to divide the join in two parts so I get a full outer join between two tables within one full outer join. But I realy want to avoid that if possible, sence the script is much bigger than shown here. And I have difficulties to get the logic to work smooth in that way.

Dale K
  • 25,246
  • 15
  • 42
  • 71
MarJer
  • 87
  • 2
  • 9
  • Could you UNION all 3 queries and then select from the resulting set? Or does there need to be some sort of hierarchy as to which attribute is returned from which table? – Jacob H Feb 26 '20 at 18:48
  • Thanks for the reply. But I have several different hierarchys in the way you are saying. – MarJer Feb 26 '20 at 18:59

1 Answers1

0

And suddenly I realised that there was one way with the "isnull" solution that I hadn't tried, which seems to work:

from t1 a
full outer join t2 b on a.attr1 = b.attr1 and a.attr2 = b.attr2
full outer join t3 c on isnull(a.attr1,b.attr1) = c.attr1 and isnull(a.attr2,b.attr2) = c.attr2

But, since this is the first time I'm using the "isnull" function in this way (in a join), I really would appreciate comments if it might create errors that can be hard to find in big datastructures/data sets. I've only tried in on a small sample now where it looks like it's working. But I won't be able to validate the results in every aspect when I've run it on the big dataset. :)

MarJer
  • 87
  • 2
  • 9