Re: Limitting full join to one match
От | Phil Endecott |
---|---|
Тема | Re: Limitting full join to one match |
Дата | |
Msg-id | 1544111190792@dmwebmail.dmwebmail.chezphil.org обсуждение исходный текст |
Ответ на | Re: Limitting full join to one match (Ron <ronljohnsonjr@gmail.com>) |
Ответы |
Re: Limitting full join to one match
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: