Reputation: 584
I have 2 SQL Server Tables Invoice and Production. Invoice contains these columns (ID, ItemID, Date, AccountNo, Quantity, Type, Price, Total) and the Production Table contains these columns (ItemID, Date, Quantity, Type) I want to combine these two table in a single table in which the ItemID, Date and Quantity are common. Like the Production Date to be viewed after the Invoice data, and later i sort by date. And the non-common fields can have a NULL value) Same as in the below example...
How to do it?
ID ItemID Date AccountNo Quantity Type Price Total
------------------------------------------------------------------------------------------
1 4 2013-06-10 123456 10 Invoice 5.00 50.00
2 7 2013-06-10 456789 15 Invoice 3.00 45.00
NULL 4 2013-06-05 NULL 40 Production NULL NULL
Upvotes: 0
Views: 93
Reputation: 1271231
You want to do a full outer join
:
select i.ID,
coalesce(i.ItemID, p.ItemId) as ItemId,
coalesce(i.Date, p.Date) as Date,
i.AccountNo,
coalesce(i.Quantity, p.Quantity) as Quantity,
p.Type, i.Price
from Invoice i full outer join
Production p
on i.ItemID = p.ItemId and
i.Date = p.Date and
i.Quantity = p.Quantity
When the second table has no match in the first, the coalesce()
makes sure that the key columns come from the second table.
Upvotes: 1