CODEWITHSUNDEEP
mysqlsqldatabase
Short Port
Short Port

Reputation: 77

Combine two tables in a new table

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!

Upvotes: 1

Views: 106

Answers (3)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

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

Upvotes: 2

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

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

Upvotes: 3

JCollerton
JCollerton

Reputation: 3327

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

Upvotes: 1

Related Questions