CODEWITHSUNDEEP
sqlsql-serverselectjoin
Arif YILMAZ
Arif YILMAZ

Reputation: 5866

merge 2 identical tables rows

I have 2 tables which have the identical columns. First table lets me store the user's bills, and the second table stores the bill cancelation. 

first table
-----------
id  - total
-----------
1   -   100
2   -    85
3   -    50

second table
-----------
id  - total
-----------
2   -    85


Result of JOIN
------------------------ 
id  - total    -  status
------------------------
1   -   100    -   OK
2   -    85    -   OK
3   -    50    -   OK
2   -    85    -   CANCEL

How can I do get the above result?

Upvotes: 1

Views: 784

Answers (3)

bastos.sergio
bastos.sergio

Reputation: 6764

Use the UNION ALL set operator to combine both tables as one.

SELECT id, total, 'OK' as status
FROM First_Table
UNION ALL
SELECT id, total, 'CANCEL' as status
FROM Second_Table

Upvotes: 3

Rom Eh
Rom Eh

Reputation: 2063

You can use the code below :

select *, 'OK' AS [Status] from [first table]
UNION ALL
select *, 'Cancel' from [second table]

Upvotes: 3

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t1.id,
       t1.total,
       'OK' AS status
FROM first t1
UNION ALL
SELECT t2.id,
       t2.total,
       'CANCEL' AS status
FROM second t2

Result:

| ID | TOTAL | STATUS |
-----|-------|--------|
|  1 |   100 |     OK |
|  2 |    85 |     OK |
|  3 |    50 |     OK |
|  2 |    85 | CANCEL |

Upvotes: 3

Related Questions