Combine two tables in a new table

Question

My two tables are:

PkID | HouseFk | house_extra_id | Price | discount_id
1    | 5       | 6              | 1205  | 0

PkID | HouseFk | PacketFk | Price | discount_id
1    | 6       | 7        | 500   | 0

How can I combine those two into a new table which is not stored in a database but only used for output. I already tried join and union all but I can't get the desired table structure. I want the columns of the first and the second table, with NULL values where needed like so:

PkID | HouseFk | house_extra_id | Price | discount_id | PacketFk
1    | 5       | 6              | 1205  | 0           | NULL
1    | 6       | NULL           | 500   | 0           | 7

If I use join on HouseFk I only get combined rows where HouseFk value is present in both tables and union all leaves out some of my columns!

possible duplicate of - http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Yaron Idan, Feb 24 '16 at 10:23

score 3 · Accepted Answer · answered Feb 24 '16 at 10:25

3

use union all, and select NULL value where you want to add extra values like this:

select PkID , HouseFk , house_extra_id , Price , discount_id, 
NULL AS PacketFk from table_1
union all
select PkID, HouseFk, NULL AS house_extra_id , Price , discount_id, 
PacketFk from table_2

answered Feb 24 '16 at 10:25

Gouda Elalfy

6,888
1
26
38

this did it, I think the secret is in the `NULL AS` part, optional question is how would you answer compare to this one: [link](http://stackoverflow.com/a/9214674/5710524) is it the same as doing `SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` UNION SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` WHERE `t1`.`id` IS NULL;` – Short Port Feb 24 '16 at 10:37

Ankit Agrawal · Answer 2 · 2016-02-24T10:34:06.463

2

it will work

select  pkid,housefk,house_extra_id,price,discount_id,null as packetfk
from T1

union all

select pkid,housefk,null as house_extra_id,price,discount_id,packetfk
from t2

edited Feb 24 '16 at 10:34

answered Feb 24 '16 at 10:26

Ankit Agrawal

2,426
1
13
27

score 1 · Answer 3 · answered Feb 24 '16 at 10:25

1

SELECT PkID, HouseFK, house_extra_id, Price, discount_id, NULL AS PacketFK FROM tableOne
UNION ALL
SELECT PkID, HouseFK, NULL AS house_extra_id, Price, discount_id, PacketFK FROM tableTwo

answered Feb 24 '16 at 10:25

JCollerton

3,227
2
20
25

Combine two tables in a new table

3 Answers3