Reputation: 77
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
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
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
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