CODEWITHSUNDEEP
mysqlsqljoin
Sweet Mae
Sweet Mae

Reputation: 15

Joining 3 tables to a single table (Joining 4 Tables)

I have 3 sales tables and 1 product table that contains all the product IDs. I need to combine the sales tables with the product table to get weekly sales.

Tables and how they relate

I have tried a few different join methods and unions. Every time I get the wrong output (either way too many or something like a cross join). Currently I have 3 different queries that I run, export those tables and manually combine the tables in excel.

An example of a join I have tried.

SELECT p1.product, p2.week, 
       p2.sales, p2.QTY,
       p3.sales, p3.QTY, 
       p4.sales, p4.QTY
FROM products_table p1 INNER JOIN aplha_sales p2 ON p1.alpha_product_id = p2.alpha_product_id
                       INNER JOIN beta_sales p3 ON p1.beta_product_id = p3.beta_product_id
                       INNER JOIN delta_sales p4 ON p1.delta_id = p4.delta_product_id
WHERE p2.week >= '2019-04-19';

I would like a table that has the following columns:

Desired Output

Upvotes: 1

Views: 754

Answers (1)

Barmar
Barmar

Reputation: 782498

You need to include the week in the joins, so that you just get that week's data from each table.

SELECT p1.product, p2.week, 
       p2.sales, p2.QTY,
       p3.sales, p3.QTY, 
       p4.sales, p4.QTY
FROM products_table p1 
INNER JOIN aplha_sales p2 ON p1.alpha_product_id = p2.alpha_product_id
INNER JOIN beta_sales p3 ON p1.beta_product_id = p3.beta_product_id AND p3.week = p2.week
INNER JOIN delta_sales p4 ON p1.delta_id = p4.delta_product_id AND p4.week = p2.week
WHERE p2.week >= '2019-04-19';

Note that this will only produce rows for weeks where all 3 products have had sales. You can use LEFT JOIN to make sales optional for 2 of the tables. But to make it optional for all 3 XXX_sales tables you need to join with another table that defines the weeks you want, and then LEFT JOIN with all the sales tables.

SELECT p1.product, w.week, 
       p2.sales, p2.QTY,
       p3.sales, p3.QTY, 
       p4.sales, p4.QTY
FROM products_table p1 
CROSS JOIN (
    SELECT week FROM alpha_sales WHERE week >= '2019-04-19'
    UNION
    SELECT week FROM beta_sales WHERE week >= '2019-04-19'
    UNION
    SELECT week FROM delta_sales WHERE week >= '2019-04-19') AS w
LEFT JOIN aplha_sales p2 ON p1.alpha_product_id = p2.alpha_product_id AND p2.week = w.week
LEFT JOIN beta_sales p3 ON p1.beta_product_id = p3.beta_product_id AND p3.week = w.week
LEFT JOIN delta_sales p4 ON p1.delta_id = p4.delta_product_id AND p4.week = w.week

The UNION in the subquery will combine all the weeks that are in any of the sales tables. By default, UNION removes duplicates, so you won't get multiple rows for each week.

Upvotes: 1

Related Questions