How to merge data with different table in SQL

Question

I have 3 different tables, I want to merge data base on the same Invoice No. from the different tables

I try some code but I have different output. My example table

table 1

table 2

table 3

MY QUERY AND OUTPUT

MY DESIRE OUTPUT

Answer 1

First create a view to mix table 1 and table 2

CREATE VIEW `view 1 2` AS
  SELECT `table 1`.invoice_no, `table 1`.shoe_brand, `table 2`.clothing_brand
    FROM `table 1` LEFT  JOIN `table 2`
    ON `table 1`.invoice_no = `table 2`.invoice_no
UNION
  SELECT `table 2`.invoice_no, `table 1`.shoe_brand, `table 2`.clothing_brand
    FROM `table 1` RIGHT JOIN `table 2`
    ON `table 1`.invoice_no = `table 2`.invoice_no;

Then create a view to mix 'view 1 2' with 'table 3'

CREATE VIEW `view 1 2 3` AS
  SELECT `view 1 2`.invoice_no, `view 1 2`.shoe_brand, `view 1 2`.clothing_brand, `table 3`.watch_brand
    FROM `view 1 2` LEFT  JOIN `table 3`
    ON `view 1 2`.invoice_no = `table 3`.invoice_no
UNION
  SELECT `table 3`.invoice_no, `view 1 2`.shoe_brand, `view 1 2`.clothing_brand, `table 3`.watch_brand
    FROM `view 1 2` RIGHT JOIN `table 3`
    ON `view 1 2`.invoice_no = `table 3`.invoice_no

Now a simple SELECT * FROM view 1 2 3; returns your result, and leave the work to MySQL.

+------------+------------+----------------+-------------+
| invoice_no | shoe_brand | clothing_brand | watch_brand |
+------------+------------+----------------+-------------+
|          1 | Nike       | GAP            | Omega       |
|          2 | Addidas    | OLD NAVY       | NULL        |
|          3 | Sperry     | NULL           | NULL        |
|          5 | NULL       | Puma           | Seiko       |
|          4 | NULL       | NULL           | Casio       |
+------------+------------+----------------+-------------+