CODEWITHSUNDEEP
sqlsql-serverouter-join
maztt
maztt

Reputation: 12294

sort of right join

I've got the following two SQL tables (in sql server):

Payment(physical payment made)
+----+-------+-----------+---------------------+
| id |Amount | BookingId | Transaction Type    |
+----+-------+-----------+---------------------+
| 1  | 10000 |   2       |   p(normal payment) |
+----+-------+-----------+---------------------+
| 2  | 90000 |   2       |   p(normal payment) |
+----+-------+-----------+---------------------+
| 3  | 8000  |   2       |   p(normal payment) |
+----+-------+-----------+---------------------+
| 4  | 8000  |   3       |   r(refunds)        |
+----+-------+-----------+---------------------+
| 5  | 10000|    4       |   r(refunds)        |
+----+-------+-----------+---------------------+

Refunds(which are going to be made in the future)

+----+--------+-----------+
| id | Amount | BookingId |
+----+--------+-----------+
| 1  | 10000  |   2       |
+----+--------+-----------+

The first table contains different payment types normal payment,refunds, token payments physically made against bookingid.

The second table contains the record of future refunds that are going to be made for that particular BookingID.

i am picking up all the refunding payment from the first table, but i want to pick up bookingId 2 as well in one dataset.

Result

+----+-------+-----------+------------+
|id  |Amount | BookingId | Difference |
+----+-------+-----------+------------+
| 4  | 8000  |    3      |  0         |
+----+-------+-----------+------------+
| 5  | 10000 |    4      |  0         |
+----+-------+-----------+------------+
| 6  |   0   |    2      |  10000     |
+----+-------+-----------+------------+

Any help would be much appreciated. booking 3 and 4 has refund but not 2

Upvotes: 2

Views: 81

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Use UNION

SELECT id, Amount, BookingId, 0 AS Difference FROM FIrstTable WHERE Transaction Type = 'R'
UNION ALL
SELECT id, 0 AS Amount, BookingId, Amount AS Difference FROM SecondTable

Upvotes: 4

Related Questions