Join three tables based on one key, putting data into same column

I have three tables that I am trying to join together to check that the proper data matches. I have table A which is a list of all accounts that a commission was paid on and what that commission amount was. I have Table B and Table C which are two tables that have commission calculations in it. The goal is to compare Table A to Table and to Table C and pulling back the amounts from both tables to ensure a match. The part I am struggling with is, Table A has all the accounts that are the base population. Table B has some and Table C as some. An account will be in either Table B or C, but never in both. I want to pull the payment from Table A, and then verify to the payment in Table B or C(whichever it occurs) and the same with commission. I then am doing a case when that compares the two fields and tells me if it matches are not.

+---------+---------+-----+------+
| Table A |         |     |      |
+---------+---------+-----+------+
| Account | Uniq_ID | Pay | Comm |
| 12345   | ABCD    | 100 | 10   |
| 23456   | OLPOL   | 25  | 2    |
| 45678   | LKJHG   | 200 | 15   |
| 96385   | LKJ67   | 250 | 26   |
+---------+---------+-----+------+
+---------+---------+-----+------+
| Table B |         |     |      |
+---------+---------+-----+------+
| Account | Uniq_ID | Pay | Comm |
| 12345   | ABCD    | 100 | 8    |
| 45678   | LKJHG   | 200 | 15   |
+---------+---------+-----+------+
+---------+---------+-----+------+
| Table C |         |     |      |
+---------+---------+-----+------+
| Account | Uniq_ID | Pay | Comm |
| 23456   | OLPOL   | 25  | 2    |
| 96385   | LKJ67   | 250 | 32   |
+---------+---------+-----+------+

I am trying to get my results to show up in a columns called pay_ver and comm_verf, and it would populate with the data from either Table B or C based on which it matched with. I am hoping to have to output look like so….

+---------+---------+-----+----------+------+-----------+---------+
| Output  |         |     |          |      |           |         |
+---------+---------+-----+----------+------+-----------+---------+
| Account | Uniq_ID | Pay | Pay_verf | comm | comm_Verf | Matched |
| 12345   | ABCD    | 100 | 100      | 10   | 8         | No      |
| 23456   | OLPOL   | 25  | 25       | 2    | 2         | Yes     |
| 45678   | LKJHG   | 200 | 200      | 15   | 15        | Yes     |
| 96385   | LKJ67   | 250 | 250      | 26   | 32        | No      |
+---------+---------+-----+----------+------+-----------+---------+

This is the code I have used to join Table A to B, and Table A to C but I have done this in two separate queries giving me two outputs. I would like to be able to do this in one, so I only have one output.

select a.account, a.uniq_id, a.pay, b.pay as pay_verf, a.comm, b.comm as comm_verf,
CASE WHEN a.comm = b.comm THEN 'MATCHED'
    ELSE 'UNMATCHED'
    END as Matched
    from tblA a
    left join tblB b
    on a.account = b.account
    and a.uniq_id = b.uniq_id;

I can not just figure out how to also get it to join to Table C without adding an extra column.

Answer

You can do:

select
  account, uniq_id, pay,
  pay_total as pay_verf,
  comm,
  comm - comm_total as comm_verf,
  case when comm = comm_total then 'Yes' else 'No' end as matched
from (
  select
    a.account, a.uniq_id, a.pay, a.comm,
    coalesce(b.pay, 0) + coalesce(c.pay, 0) as pay_total,
    coalesce(b.comm, 0) + coalesce(c.comm, 0) as comm_total
  from table_a a
  left join table_b b on a.account = b.account
  left join table_c c on a.account = c.account
) x

Advertisement

Answer