CODEWITHSUNDEEP
sqlsql-server
Jake
Jake

Reputation: 604

SQL Server 2014 - Want to JOIN, but records may not be on one table or the other

I have two tables as follows:

CashDrawer table
+--------+---------------+-----------------+-----------+
| UserID | CashDeposited | ChecksDeposited | Timestamp |
+--------+---------------+-----------------+-----------+
|      1 |             5 |              10 | 5/26/2017 |
|      2 |             5 |              10 | 5/26/2017 |
|      3 |             5 |              10 | 5/26/2017 |
+--------+---------------+-----------------+-----------+

OtherPayments table
+--------+---------------+-----------+
| UserID | OtherPayments | Timestamp |
+--------+---------------+-----------+
|      4 |            15 | 5/26/2017 |
|      4 |            15 | 5/26/2017 |
|      4 |            15 | 5/26/2017 |
+--------+---------------+-----------+

Records on the CashDrawer table show total cash or checks deposited for a particular day. Records on the OtherPayments table show individual payments that have to be summed.

Sometimes UserID 4 will accept cash and checks and will be on the CashDrawer table. Sometimes UserIDs 1, 2, and 3 will sometimes accept other payments and will be on the OtherPaymentsTable. The UserID and Timestamp fields are the key between these two tables, but I'm not sure how to get this output or if it's even possible:

+--------+---------------+-----------------+---------------+-----------+
| UserID | CashDeposited | ChecksDeposited | OtherPayments | Timestamp |
+--------+---------------+-----------------+---------------+-----------+
|      1 |             5 |              10 |             0 | 5/26/2017 |
|      2 |             5 |              10 |             0 | 5/26/2017 |
|      3 |             5 |              10 |             0 | 5/26/2017 |
|      4 |             0 |               0 |            45 | 5/26/2017 |
+--------+---------------+-----------------+---------------+-----------+

Upvotes: 1

Views: 31

Answers (1)

dnoeth
dnoeth

Reputation: 60472

You need to aggregate over two UNIONed Selects like this:

select
   UserID,
   sum(CashDeposited),
   sum(ChecksDeposited),
   sum(OtherPayments),
   Timestamp
from
 ( -- UNION needs the same number of columns in both Selects
   select
      UserID,
      CashDeposited,
      ChecksDeposited,
      0 AS OtherPayments,-- doesn't exist in this table
      Timestamp
   from CashDrawer

   union all

   select
      UserID,
      0 AS CashDeposited,  -- doesn't exist in this table 
      0 AS ChecksDeposited,-- doesn't exist in this table
      OtherPayments,
      Timestamp
   from OtherPayments
 ) as dt
group by UserID, Timestamp

Upvotes: 1

Related Questions