Re: combination join against multiple tables

On Fri, 31 Jan 2020 14:01:17 +0000
Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> a | c | c
> ----+-----+-----
> 1 | 111 | 211
> 1 | 112 |
> 2 | 121 |
> 2 | | 222
> 3 | |
> 4 | 141 |
> 5 | | 253
> 6 | |
> 7 | |
> 8 | |
> 9 | |
> 10 | |

The c's look just like a full outer join of t1 & t2 on a & b.

Giving them saner names to avoid duplicate output col's, let's
call them "c1" & "c2".

At that point a left outer join on a gives you all of the base.a
values with any t{1,2} rows that have a matching a:

No idea what your data really looks like but if t1 or t2 has more
than three col's distinct can save some annoying cross-products:

select
distinct
base.a
, z.c1
, z.c2
from
base
left join
(
select
t1.a
, t1.c "c1"
, t2.c "c2"
from
t1
full outer join
t2
on
t1.a = t2.a
and
t1.b = t2.b
) z
on
base.a = z.a
;

--
Steven Lembark 3646 Flora Place
Workhorse Computing St. Louis, MO 63110
lembark(at)wrkhors(dot)com +1 888 359 3508