Re: Limitting full join to one match

On Wed, Dec 5, 2018 at 4:34 PM Phil Endecott <
spam_from_pgsql_lists(at)chezphil(dot)org> wrote:

> Dear Experts,
>
> I have a couple of tables that I want to reconcile, finding rows
> that match and places where rows are missing from one table or the
> other:
>
> ...

> So my question is: how can I modify my query to output only two rows,
> like this:?
>
> +------------+--------+------------+--------+
> | date | amount | date | amount |
> +------------+--------+------------+--------+
> | 2018-01-01 | 10.00 | 2018-01-01 | 10.00 |
> | 2018-02-01 | 5.00 | | |
> | | | 2018-03-01 | 8.00 |
> | 2018-04-01 | 5.00 | 2018-04-01 | 5.00 |
> | 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 1
> | 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 2
> +------------+--------+------------+--------+
>
>
Evening Phil,

Window functions are your friend here. I prefer views for this stuff - but
subqueries would work just fine.

create view a_rows as (select *,
row_number() OVER (PARTITION BY date, amount) AS pos
from a);
create view b_rows as (select *,
row_number() OVER (PARTITION BY date, amount) AS pos
from b);

select
a_rows.date,
a_rows.amount,
a_rows.pos,
b_rows.date,
b_rows.amount,
b_rows.pos
from
a_rows full join b_rows using (date,amount,pos);

Example here - http://sqlfiddle.com/#!17/305d6/3

John

>
> Any suggestions anyone?
>
>
> The best I have found so far is something involving EXCEPT ALL:
>
> db=> select * from a except all select * from b;
> db=> select * from b except all select * from a;
>
> That's not ideal, though, as what I ultimately want is something
> that lists everything with its status:
>
> +------------+--------+--------+
> | date | amount | status |
> +------------+--------+--------+
> | 2018-01-01 | 10.00 | OK |
> | 2018-02-01 | 5.00 | a_only |
> | 2018-03-01 | 8.00 | b_only |
> | 2018-04-01 | 5.00 | OK |
> | 2018-05-01 | 20.00 | OK |
> | 2018-05-01 | 20.00 | OK |
> +------------+--------+--------+
>
> That would be easy enough to achieve from the JOIN.
>
>
> Thanks, Phil.
>
>
>
>