Re: combination join against multiple tables

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).

David J.