From: | "Phil Endecott" <spam_from_pgsql_lists(at)chezphil(dot)org> |
---|---|
To: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Limitting full join to one match |
Date: | 2018-12-06 00:34:03 |
Message-ID: | 1544056443924@dmwebmail.dmwebmail.chezphil.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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:
db=> select * from a;
+------------+--------+
| date | amount |
+------------+--------+
| 2018-01-01 | 10.00 |
| 2018-02-01 | 5.00 | <-- missing from b
| 2018-04-01 | 5.00 |
+------------+--------+
db=> select * from b;
+------------+--------+
| date | amount |
+------------+--------+
| 2018-01-01 | 10.00 |
| 2018-03-01 | 8.00 | <-- missing from a
| 2018-04-01 | 5.00 |
+------------+--------+
db=> select a.date, a.amount, b.date, b.amount from a full join b using (date,amount);
+------------+--------+------------+--------+
| 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 |
+------------+--------+------------+--------+
This works fine until I have multiple items with the same date
and amount:
db=> select * from a;
+------------+--------+
| date | amount |
+------------+--------+
| 2018-01-01 | 10.00 |
| 2018-02-01 | 5.00 |
| 2018-04-01 | 5.00 |
| 2018-05-01 | 20.00 | <--
| 2018-05-01 | 20.00 | <--
+------------+--------+
db=> select * from b;
+------------+--------+
| date | amount |
+------------+--------+
| 2018-01-01 | 10.00 |
| 2018-03-01 | 8.00 |
| 2018-04-01 | 5.00 |
| 2018-05-01 | 20.00 | <--
| 2018-05-01 | 20.00 | <--
+------------+--------+
db=> select a.date, a.amount, b.date, b.amount from a full join b using (date,amount);
+------------+--------+------------+--------+
| 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
| 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 3
| 2018-05-01 | 20.00 | 2018-05-01 | 20.00 | 4
+------------+--------+------------+--------+
It has, of course, put four rows in the output for the new items.
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
+------------+--------+------------+--------+
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John W Higgins | 2018-12-06 03:30:26 | Re: Limitting full join to one match |
Previous Message | Alvaro Herrera | 2018-12-05 20:37:27 | Re: simple division |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2018-12-06 02:14:06 | Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0 |
Previous Message | Nikita Glukhov | 2018-12-06 00:00:07 | Re: [PATCH] Opclass parameters |