How do I combine two partially overlapping lists in MySQL?
OK, so I have two tables (actually) that have the same structure. Each has a date column and a numeric column.
The dates in both tables will be nearly continuous and cover the same period. Consequently, the data will be mostly the same, but the date in one table may not appear in another.
I want to have one table with one date column and two numeric columns. If there is no date in one table, the corresponding numeric field must be null.
Any approaches?
PS This is not the same question: How to merge two MySQL tables?
a source to share
VladiatOr's solution can be shortened by the fact that the first batch selection records are present both in the first and only in the first table, and then those that are present only in the second table are added.
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
UNION
SELECT t2.td, t1.val as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL
See also Tobias Riemenschneider's comment in MySQL JOIN Syntax Reference .
a source to share
This query is a join of three queries: The first joins the records that are common in both tables. The second adds those that only exist in table1. The third adds records that are only in table2.
SELECT t1.td, t1.val as val1, t2.val as val2
FROM table1 as t1, table2 as t2
WHERE t1.dt = t2.dt
UNION
SELECT t1.td, t1.val as val1, null as val2
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.td = t2.td
WHERE t2.td IS NULL
UNION
SELECT t2.td, null as val1, t2.val as val2
FROM table2 as t2
LEFT JOIN table1 as t1
ON t2.td = t1.td
WHERE t1.td IS NULL
a source to share