Re: combination join against multiple tables

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

> Hi
>
> I have a query involving multiple tables that I would like to return
> in a single query. That means returning multiple sets of the data
> from the first base table, but that's acceptable for the simplicity
> in grabbing all the data in one hit.
>
> An example set:
>
> CREATE TABLE t1 (a int, b int, c int);
> CREATE TABLE t2 (a int, b int, c int);
> CREATE TABLE base (a int);
> INSERT INTO t1 (a, b, c) VALUES (1, 1, 111), (1,2,112), (2,1,121),
> (4,1,141);
> INSERT INTO t2 (a, b, c) VALUES (1, 1, 211), (2, 2, 222), (5,3,253);
> INSERT INTO base(a) SELECT * FROM GENERATE_SERIES(1,10);
>
> Now the problem is that I would like to return all the rows from a,
> but with a single row where t2.b and t1.b match.
>
> So the results I would like:
>
> a | c | c
> ----+-----+-----
> 1 | 111 | 211
> 1 | 112 |
> 2 | 121 |
> 2 | | 222
> 3 | |
> 4 | 141 |
> 5 | | 253
> 6 | |
> 7 | |
> 8 | |
> 9 | |
> 10 | |
>
> At the moment I'm doing
>
> SELECT base.a, t1.c, t2.c
> FROM base
> CROSS JOIN (SELECT b FROM t1 UNION SELECT b FROM t2 UNION SELECT -1)
> tmpset LEFT JOIN t1 ON t1.a=base.a AND t1.b=tmpset.b
> LEFT JOIN t2 ON t2.a=base.a AND t2.b=tmpset.b
> WHERE t1.a IS NOT NULL
> OR t2.a IS NOT NULL
> OR (tmpset.b=-1
> AND NOT EXISTS (SELECT FROM t1 WHERE t1.a=base.a)
> AND NOT EXISTS (SELECT FROM t2 WHERE t2.a=base.a)
> );
>
>
> but this seems like a really convoluted way to do it.
>
> Is there a join style that will return the set I want without the
> pain?
>
> I should be clear that the real world data is much more complex than
> this, but it describes the basic problem.
>
> Thanks
>
> Geoff

The join on T1 & t2 seems to just be a full outer join of
t1 & t2 on a & b.

Note that you cannot have two columns in the output with the
same name (e.g., a+c+c, above, is not a valid table). Call them
"c1" & "c2":

A full outer join of t1 & t2 on a & b seems to give you all of
the necessary combinations of c necessary; at which point an
outer join on a associates base values with anything that
mathes on a:

select
distinct
base.a
, z.c1
, z.c2
from
base
left join
(
select
distinct
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
;

No idea what the real data looks like, but distinct likely to be
helpful if real t's have more than three cols.

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