Re: combination join against multiple tables

On Fri, 31 Jan 2020 at 15:25, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Friday, January 31, 2020, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
>
>> 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 final,number of rows for each “a” is the larger row count of “b”
> and “c” having the same “a”. Furthermore for the first “n” rows “b” and
> “c” should be paired together by position. The smaller count column just
> gets nulls for the extra rows.
>
> Probably the easiest way is to combine the matches for “b” and “c” into
> arrays the jointly unnest those arrays in the final result - with in the
> select list or maybe as part,of a lateral join, not sure without
> experimentation.
>
> Otherwise you can add “row_number” to “b” and “c” and then left join on
> (a, row_number).
>
>
Thanks for the reply. Using array() hadn't occurred to me, I'll look at
that.

I actually came up with this:

SELECT base.a, t1.c, t2.c
FROM base
LEFT JOIN (t1 FULL OUTER JOIN t2 ON t1.b=t2.b AND t1.a=t2.a)
ON COALESCE(t1.a, base.a)=base.a AND COALESCE(t2.a, base.a)=base.a;

which does solve the described problem; sadly I realise that I'd
oversimplified my question: I haven't fully described the problem because
in reality "t2" is joined to "base" with a different field, and I can't
seem to get the join to do what I want without joining them together like
this.

Geoff