sql matching tables with different records via id-CodePudding

I am trying to match 3 tables each containing different values but with the same id and i want to have a mixed result (final table) as shown in the image. How can i achieve that?

CodePudding user response：

You can get all products from all table without duplicates and save them to temp table. Then simply LEFT JOIN temp table with three tables.

DROP TEMPORARY TABLE IF EXISTS all_products;
CREATE TEMPORARY TABLE all_products
SELECT DISTINCT id, product FROM (
  SELECT id, product FROM table1 UNION
  SELECT id, product FROM table2 UNION
  SELECT id, product FROM table3
) AS t;

ALTER TABLE all_products ADD INDEX id(id);

SELECT 
  t.id, t1.product, 
  t1.`count` AS `count table 1`,
  t2.`count` AS `count table 2`,
  t3.`count` AS `count table 3`
FROM
  all_products AS t
  LEFT JOIN table1 AS t1 ON t1.id=t.id
  LEFT JOIN table2 AS t2 ON t2.id=t.id
  LEFT JOIN table3 AS t3 ON t3.id=t.id
;

You can also use a single query like that:

SELECT
  t.id, t.product, 
  t1.`count` AS `count table 1`,
  t2.`count` AS `count table 2`,
  t3.`count` AS `count table 3`
FROM (
  SELECT DISTINCT id, product FROM (
    SELECT id, product FROM table1 UNION
    SELECT id, product FROM table2 UNION
    SELECT id, product FROM table3
  ) AS a
) AS t
  LEFT JOIN table1 AS t1 ON t1.id=t.id
  LEFT JOIN table2 AS t2 ON t2.id=t.id
  LEFT JOIN table3 AS t3 ON t3.id=t.id
;