On Fri, 31 Jan 2020 at 15:25, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Friday, January 31, 2020, Geoff Winkless <pgsqlad...@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
