Hi Ron,
Ron wrote:
On 12/05/2018 06:34 PM, Phil Endecott 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:
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 | <--
+------------+--------+
What's your PK on "a" and "b"?
These input tables can have duplicate rows, so defining a primary key
requires something like a row ID or similar.
(Also, gmail seems to think that all -- or at least most -- of your email is
spam.)
Yes, it is becoming increasingly difficult to persuade gmail etc. that
you are not a spammer if you run your own mail server. If you have any
interesting headers suggesting exactly what they disliked about my message,
could you please forward them off-list? Thanks.
Regards, Phil.
