Query data from two tables and select only modified records

1
votes

What i'm trying to do is:

I have one TableA contains all records.

account | name | items | amount
0017532      A       3     1500
0034524      B       5      700
7772618      C       7      300
5467382      D       2      450
7772618      C       6     2200

another TableB contains:

account | name | items | amount
0034524      B       2      200
5467382      D       1       50
6483232      E       1       25

Desired query result is:

account | name | items | amount
0017532      A       3     1500
0034524      B       3      500
7772618      C       7      300
5467382      D       1      400
6483232      E       1       25
7772618      C       6     2200

If there's record in TableB then TableA.item - TableB.item and TableB.amount - TableB.amount

The result should contain all record from table A and the result of above subtraction.

I join TableA and TableB and perform minus operation but the result return only for the records that match with number of record in TableB

account | name | items | amount
0034524      B       3      500
5467382      D       1      400
6483232      E       1       25

When I try UNION above subtraction query with query TableA, it turned out that the record for both table are listed all:

account | name | items | amount
0017532      A       3     1500
0034524      B       5      700
0034524      B       3      500
7772618      C       7      300
5467382      D       2      450
5467382      D       1      400
7772618      C       6     2200
6483232      E       1       25

I'm trying to find a way to select only records from TableB if its 'account' and 'name' exist in TableA.

2
sql
Could you also post the queries you wrote? - Himanshu Tyagi
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ... - a_horse_with_no_name

2 Answers

0
votes

I think the simplest way to get what you want uses a left join and union all:

select a.account, a.name,
       (a.items - coalesce(b.items, 0)) as items,
       (a.amount - coalesce(b.amount, 0)) as amount
from a left join
     b
     on a.account = b.account
union all
select b.account, b.name, b.items, b.amount
from b
where not exists (select 1
                  from a
                  where a.account = b.account
                 );

You can also use a full join, but the logic is cumbersome because of the signs on the data from b:

select coalesce(a.account, b.account) as account,
       coalesce(a.name, b.name) as name,
       (case when a.account is null then b.items
             else a.items - coalesce(b.items, 0)
        end) as items,
       (case when a.account is null then b.amount
             else a.amount - coalesce(b.amount, 0)
        end) as items
from a full join
     b
     on a.account = b.account;
0
votes

If the accounts are not unique in each table?
Then you probably want to substract B for A only once.

In that case you could also FULL JOIN them on account and a calculated row_number.

Assuming your database supports window functions like row_number
Btw, i.m.h.o. those expected results seem misleading to me

Sample Data

create table TableA (account char(7), name char(1), items integer, amount integer);
create table TableB (account char(7), name char(1), items integer, amount integer);

insert into TableA (account, name, items, amount) values
('1111111', 'A', 10, 100),
('2222222', 'B', 20, 200),
('3333333', 'C', 30, 310),
('3333333', 'C', 30, 320),
('4444444', 'D', 40, 400),
('6666666', 'F', 60, 610),
('6666666', 'F', 60, 620);

insert into TableB (account, name, items, amount) values
('2222222', 'B', 10, 100),
('4444444', 'D', 10, 100),
('5555555', 'E', 10, 100),
('6666666', 'F', 10, 100);

Query:

select 
coalesce(a.account, b.account) as Account, 
coalesce(a.name, b.name) as Name, 
coalesce(case when a.items is not null and b.items is not null then a.items - b.items end, a.items, b.items) as Items,
coalesce(case when a.amount is not null and b.amount is not null then a.amount - b.amount end, a.amount, b.amount) as Amount
from 
(
   select account, name, items, amount,
    row_number() over (partition by account order by amount) as rn
   from TableA
) as a
full join
(
   select account, name, items, amount,
    row_number() over (partition by account order by amount) as rn
   from TableB
) as b on (B.account = A.account and B.rn = A.rn)
order by Account;