How to Merge SQL Tables and Ensure Unique Date Values?

Merging Tables with Unique Dates

In SQL, merging two tables while maintaining unique date values is a common task. Here's a solution to your problem of merging the Inbound and Outbound tables:

The first step is to merge the tables using the UNION ALL operator. This operator combines the rows from both tables without removing duplicates:

(SELECT Inbound_Date As Date, Product, SUM(Quantity) as Inbound, 0 as Outbound
FROM Inbound
GROUP BY 1,2
) 

UNION ALL

(SELECT Outbound_Date, Product, 0 as Inbound, COUNT(*)  as Outbound 
FROM Outbound
GROUP BY 1,2
)

Next, we need to eliminate duplicate dates. For this, we use the GROUP BY clause to consolidate rows with the same date and product:

SELECT Date, Product, SUM(Inbound) as Inbound, SUM(Outbound) as Outbound
FROM ((SELECT Inbound_Date As Date, Product, SUM(Quantity) as Inbound, 0 as Outbound
      FROM Inbound
      GROUP BY 1,2
     ) UNION ALL
     (SELECT Outbound_Date, Product, 0 as Inbound, COUNT(*)  as Outbound 
      FROM Outbound
      GROUP BY 1,2
     )
    ) io
GROUP BY Date, Product;

This final query results in a table with unique date values, as desired. The output will be similar to the example you provided:

Date           Product       Inbound        Outbound
2017-05-13     Product A     400            1
2017-09-04     Product C     380            0
2017-10-18     Product C      0             1
:              :             :              :
:              :             :              :
2018-09-10     Product B     200            1
:              :             :              :
:              :             :              :

The above is the detailed content of How to Merge SQL Tables and Ensure Unique Date Values?. For more information, please follow other related articles on the PHP Chinese website!