CODEWITHSUNDEEP
sqloracle-databasejoinmerge
FK IE
FK IE

Reputation: 5

How to combine 2 tables into one?

I have two tables (A) & (B), with almost the same information at different dates.

TABLE (A)

+----+------------+----------+-------+
|ID  | Date       | Status   | Amount|
+----+------------+----------+-------+
| A1 | 01/14/2011 | Received |15     |
| A2 | 01/18/2011 | Received |65     |
| T3 | 01/9/2011  | Received |85     |
| X7 | 02/16/2011 | Received |35     |
| A5 | 01/3/2011  | Received |10     |
+----+------------+----------+-------+

TABLE (B)

+----+------------+----------+-------+
|ID  | Date       | Status   | Amount|
+----+------------+----------+-------+
| A1 | 03/31/2012 | Approved |15     |
| A2 | 03/31/2012 | Denied   |65     |
| A3 | 03/31/2012 | Approved |85     |
| A7 | 03/31/2012 | Received |35     |
| A5 | 03/31/2012 | pending  |10     |
+----+------------+----------+-------+

I am trying to merge them in order to obtain a new table (result) like this:

TABLE (RESULT)

+----+------------+----------+-------+
|ID  | Date       | Status   | Amount|
+----+------------+----------+-------+
| A1 | 01/14/2011 | Received |15     |
| A1 | 03/31/2012 | Approved |15     |
| A2 | 01/18/2011 | Received |65     |
| A2 | 03/31/2012 | Denied   |65     |
| A5 | 01/3/2011  | Received |10     |
| A5 | 03/31/2012 | pending  |10     |
+----+------------+----------+-------+

I used this code:

SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id
ORDER BY table1.id;

Here is the result:

+----+------------+----------+-------+------------+------------+---------+
| ID | Date       | Status   | Amount| Date_1     | Status_1   | Amount_1|
+----+------------+----------+-------+------------+------------+---------+
| A1 | 01/14/2011 | Received |15     |03/31/2012  | Approved   |15       |
+----+------------+----------+-------+------------+------------+---------+

It's working fine but it's not what I am looking for. I don't want duplicate columns side by side. I was thinking about using INSERT INTO or UNION ALL but I am not sure.

Upvotes: 1

Views: 90

Answers (6)

William Robertson
William Robertson

Reputation: 15991

If the aim (which you haven't really described) is to report the rows from either table whose ID exists in both tables, then perhaps you want this: 

select id, somedate, status, amount
from   ( select id, somedate, status, amount from table_a
         union all
         select id, somedate, status, amount from table_b )
where  id in
       ( select id from table_a
         intersect
         select id from table_b )

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Here is a simple union all query to do the job:

select * from a where id in (select id from b)
union all
select * from b where id in (select id from a)
order by id, date;

Upvotes: 1

Iustin Beceneagă
Iustin Beceneagă

Reputation: 153

select x.id, x.status, x.amount, y.status, y.amount
from 
(
select 'A1' id, 'received' status, 15 amount from dual
union all
select 'A2' id, 'received' status, 65 amount from dual
) x,
 (
select 'A1' id, 'approved' status, 15 amount from dual
union all
select 'A2' id, 'denied' status, 65 amount from dual
) y
where x.id = y.id

Hope it helps!

Upvotes: 0

forpas
forpas

Reputation: 164064

With UNION including from each table only rows with corresponding rows in the other table:

SELECT * FROM TableA a
WHERE EXISTS (
  SELECT 1 FROM TableB
  WHERE ID = a.ID AND Amount = a.Amount AND Status <> a.Status
)
UNION
SELECT * FROM TableB b
WHERE EXISTS (
  SELECT 1 FROM TableA
  WHERE ID = b.ID AND Amount = b.Amount AND Status <> b.Status
)
ORDER BY ID;

I am not sure about the condition Amount = x.Amount. Maybe you want to remove it.

Upvotes: 1

Niharika Bitra
Niharika Bitra

Reputation: 477

Try this:

SELECT t2.*
FROM table1 t1
INNER JOIN 
(
    --Get results from both tables
    SELECT *
    FROM table1
    UNION ALL
    SELECT *
    FROM table2
) t2
    on t1.ID = t2.ID
ORDER BY t2.ID;

Upvotes: 0

wavery
wavery

Reputation: 279

Use a UNION:

SELECT ID, Date, Status, Amount
FROM table1
UNION
SELECT ID, Date, Status, Amount
from table2
ORDER BY ID;

Upvotes: -1

Related Questions