On Friday, January 31, 2020, Geoff Winkless <pgsqladmin@geoff.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