How to Union Uncommon Columns in SQL

Question

What SELECT Statement should I write to get desired table from Tabel1 & 2

Table 1 - Charge Table

Table 2 - Order Table

Desired Table

Answer 1

Use Union as below:

DECLARE @tblCharge AS Table
(
    ChargeId INT,
    BillingId INT,
    OrderId INT,
    ChargeName VARCHAR(50),
    Amount Decimal(18,2)
)

DECLARE @tblOrder AS Table
(   
    OrderId INT,
    ProductName VARCHAR(50),
    Price Decimal(18,2)
)

INSERT INTO @tblCharge
(
    ChargeId,
    BillingId ,
    OrderId ,
    ChargeName ,
    Amount
)
VALUES
(1,1,1,'ServiceFee',2.00),
(2,1,2,'ServiceFee',3.00),
(3,1,3,'ServiceFee',2.00),
(4,1,3,'BookingFee',2.00),
(5,2,4,'ServiceFee',1.00),
(6,2,5,'ServiceFee',1.00)

INSERT INTO @tblOrder
(
    OrderId ,
    ProductName ,
    Price
)
VALUES
(1,'Sandwich',5.00),
(2,'Coke',2.50),
(3,'Cake',5.00),
(4,'Water',3.00),
(5,'Sandwich',7.00)

SELECT
    ChargeId,
    BillingId ,
    OrderId ,
    ChargeName ,
    Amount
FROM @tblCharge C
UNION
SELECT
    C.ChargeId,
    C.BillingId ,
    O.OrderId ,
    O.ProductName ,
    O.Price
FROM @tblOrder O 
LEFT JOIN @tblCharge C ON C.OrderId=O.OrderId

Answer 2

what you're doing here doesn't make too much sense..

First of all, a union cannot be used in the manner you are attempting to apply it. See Microsoft's documentation on Unions here:

https://msdn.microsoft.com/en-us/library/ms180026.aspx

If you want to combine two tables on conditions where the tables match, trying using an INNER JOIN:

SELECT a.ChargeId, a.BillingId, a.OrderId a.ChargeName, a.Amount, b.ProductName, b.Price
FROM Table1 a
INNER JOIN Table2 b ON a.OrderId = b.OrderId

The end result is different to the table you desire but makes more logical sense than what you are attempting to return.

Answer 3

Try with the below query..

;WITH cte_1
 as
 (SELECT ChargeID,BillingId,o.OrderID,ChargeName,ProductName,Amount,Price
 FROM Charge c
   JOIN [Order] o on c.OrderID=o.OrderID)
SELECT ChargeID,BillingId,OrderID,ChargeName,Amount
FROM cte_1
UNION 
SELECT ChargeID,BillingId,OrderID,ProductName,Price
FROM cte_1
ORDER BY OrderID

Answer 4

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order