CODEWITHSUNDEEP
mysqlsql
NNOPP
NNOPP

Reputation: 107

How to perform complex calculation and update to a view in MySQL?

I want to calculate how many Car_A and Car_B can be built with my existing items in warehouse?
Expecting to see the Car_A and Car_B with maximum number that I can build in the view below.

Please let me know if table design is bad or else.

What I have done: What I use everyday is export to Excel and calculate in Excel manually.

enter image description here

Upvotes: 0

Views: 257

Answers (1)

CanvasB
CanvasB

Reputation: 61

First I would suggest you to split the car and tool in the warehouse. You can just create another column and put some sign for it like C = Car and T = Tools Or you can create another table and put your car in it I prefer this way and my answer will goes this way and this is mydata test

CREATE TABLE [dbo].[Assembly](
    [id_item] [int] NULL,
    [name] [varchar](100) NULL,
    [quantity] [int] NULL
) 
insert into [Assembly]
select 1,'Car_A',0
union all
select 2,'Car_B',0

CREATE TABLE [dbo].[Warehouse](
    [id_item] [int] NULL,
    [name] [varchar](100) NULL,
    [quantity] [numeric](18, 2) NULL
)
insert into [Warehouse]
select 1,'Door',30
union all
select 2,'Wheel',30
union all
select 3,'Light',30

CREATE TABLE [dbo].[Relation](
    [assembly_id] [int] NULL,
    [required_item] [int] NULL,
    [required_quantity] [int] NULL
)
insert into [relation]
select 1,1,10
union all
select 1,2,10
union all
select 1,3,10
union all
select 2,1,3
union all
select 2,2,3
union all
select 2,3,3

testing the relation

select * from Assembly as a
inner join Relation as r on r.assembly_id = a.id_item
inner join Warehouse as w on w.id_item = r.required_item

The result is

id_item name    quantity    assembly_id required_item   required_quantity   id_item name    quantity
1   Car_A   0   1   1   10  1   Door    30.00
1   Car_A   0   1   2   10  2   Wheel   30.00
1   Car_A   0   1   3   10  3   Light   30.00
2   Car_B   0   2   1   3   1   Door    30.00
2   Car_B   0   2   2   3   2   Wheel   30.00
2   Car_B   0   2   3   3   3   Light   30.00

and here your solution

select a.name,min(a.new_quantity) as new_quantity 
from (
select a.name,floor(w.quantity/r.required_quantity) as new_quantity 
from Assembly as a
inner join Relation as r on r.assembly_id = a.id_item
inner join Warehouse as w on w.id_item = r.required_item
) as a
group by a.name

name    new_quantity
Car_A   3
Car_B   10

you can try editing your tools left to see if it's correct or not. Hope this help:)

Upvotes: 1

Related Questions