mysql true row merge... not just a union
- by panofish
What is the mysql I need to achieve the result below given these 2 tables:
table1:
+----+-------+
| id | name |
+----+-------+
| 1 | alan |
| 2 | bob |
| 3 | dave |
+----+-------+
table2:
+----+---------+
| id | state |
+----+---------+
| 2 | MI |
| 3 | WV |
| 4 | FL |
+----+---------+
I want to create a temporary view that looks like this
desired result:
+----+---------+---------+
| id | name | state |
+----+---------+---------+
| 1 | alan | |
| 2 | bob | MI |
| 3 | dave | WV |
| 4 | | FL |
+----+---------+---------+
I tried a mysql union but the following result is not what I want.
create view table3 as
(select id,name,"" as state from table1)
union
(select id,"" as name,state from table2)
table3 union result:
+----+---------+---------+
| id | name | state |
+----+---------+---------+
| 1 | alan | |
| 2 | bob | |
| 3 | dave | |
| 2 | | MI |
| 3 | | WV |
| 4 | | FL |
+----+---------+---------+
First suggestion results:
SELECT *
FROM table1
LEFT OUTER JOIN table2 USING (id)
UNION
SELECT *
FROM table1
RIGHT OUTER JOIN table2 USING (id)
+----+---------+---------+
| id | name | state |
+----+---------+---------+
| 1 | alan | |
| 2 | bob | MI |
| 3 | dave | WV |
| 2 | MI | bob |
| 3 | WV | dave |
| 4 | FL | |
+----+---------+---------+
