CODEWITHSUNDEEP
c#sql-serverwinforms
fadd
fadd

Reputation: 584

Combine 2 SQL Server Tables in one table

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions