MySQL – Getting Product Stock of All Types in Single Query

MySQLmysql-5.6mysql-5.7

I want to get stock information for all the products depending upon the product type.
The tables are products, product_stocks, complex1_childs & complex2_childs.
products 

| product_id | type      |
|------------|-----------|
| 100        | simple    |
| 101        | simple    |
| 103        | complex_1 |
| 104        | simple    |
| 105        | complex_2 |
| 106        | complex_1 |
| 107        | simple    |
| 108        | simple    |
| 109        | simple    |

The available product types are:

  • simple
  • complex_1
  • complex_2

complex_* type is a compound product which includes other products as a child.

product_stocks

| product_id | qty |
|------------|-----|
| 100        | 10  |
| 101        | 50  |
| 104        | 10  |
| 107        | 25  |
| 108        | 100 |
| 109        | 40  |

As you can see only simple product type have qty info.

complex1_childs

| parent_id | child_id |
|-----------|----------|
| 103       | 101      |
| 106       | 107      |
| 106       | 101      |

Child products are always simple type.

complex2_childs

| parent_id | child_id |
|-----------|----------|
| 105       | 107      |
| 105       | 108      |

Child products are always simple type.

Now I want to get stock information for all the products as:

| product_id | final_qty |
|------------|-----------|
| 100        | 10        |
| 101        | 50        |
| 103        | 50        |
| 104        | 10        |
| 105        | 125       |
| 106        | 75        |
| 107        | 25        |
| 108        | 100       |
| 109        | 40        |

Note that qty for complex_* product types are calculated as sum of their child products' qty.

Best Answer

You have effectively have these 3 queries:

simple

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN product_stocks AS s
  ON p.product_id = s.product_id AND
     p.type = 'simple'
GROUP BY p.product_id

complex 1:

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN complex1_childs AS c
  ON p.product_id = c.parent_id AND
     p.type = 'complex_1'
JOIN products AS cp
  ON c.child_id = cp.product_id
JOIN product_stocks AS s
  ON cp.product_id = s.product_id
GROUP BY p.product_id

complex 2 is the same as above changing the table and the p.type join criteria.

You can UNION these together into a single query.

Related Question