Search Postgresql Archives

Re: Limitting full join to one match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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"?

(Also, gmail seems to think that all -- or at least most -- of your email is spam.)

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.




--
Angular momentum makes the world go 'round.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux