Hello. I am developing program in python and need to do some complex MYSQL queries involving 3 tables.
TABLE1 - consists of "recipe" data. It describes what serial numbers and quantities we need
TABLE2 - Imagine this to be person1. This person has some serial numbers assigned to himself.
TABLE3 - Imagine this to be person2. This person also has some serial numbers assigned to himself.
Now the task involves checking TABLE1 and finding how many quantities we need for every unique serial number.
Use this as an example:
https://www.db-fiddle.com/f/8eSUQCwM3NwNDSHJmATycp/3
If you run this example, you see that we have:
"55555" - 2 quantities
"12345" - 1 quantity
"33333" - 2 quantities
Now that we know what the recipe consists of, I must check whether the person1 and person2 have these items. It does not matter what person1 and person2 have individually, as long as both summed up together have the required amount it is ok.
Keep in mind that the actual quantity column in table2 and table3 does not have anything to do with it. We must have equal or more number of unique rows with that particual serial number.
From the example that I have linked, you can see that table2 (person1) has:
2 quantities of "55555"
1 quantity of "33333"
In this scenario, I refer to quantity as unique row
And the table3 (person2) has:
1 quantity of "33333"
1 quantity of "12345"
The total quantities between both persons (table2 and table3) is :
2 quantities of "55555"
2 quantities of "33333"
1 quantity of "12345"
Which is exactly as required by the recipe, so everything is fine, however, if something is missing, the program should return an error.
I really cant get my head around on how to build this query to sum all the serials between the person1 and person2 and compare it to the recipe. Can someone help please?
TABLE1 - consists of "recipe" data. It describes what serial numbers and quantities we need
TABLE2 - Imagine this to be person1. This person has some serial numbers assigned to himself.
TABLE3 - Imagine this to be person2. This person also has some serial numbers assigned to himself.
Now the task involves checking TABLE1 and finding how many quantities we need for every unique serial number.
Use this as an example:
https://www.db-fiddle.com/f/8eSUQCwM3NwNDSHJmATycp/3
If you run this example, you see that we have:
"55555" - 2 quantities
"12345" - 1 quantity
"33333" - 2 quantities
Now that we know what the recipe consists of, I must check whether the person1 and person2 have these items. It does not matter what person1 and person2 have individually, as long as both summed up together have the required amount it is ok.
Keep in mind that the actual quantity column in table2 and table3 does not have anything to do with it. We must have equal or more number of unique rows with that particual serial number.
From the example that I have linked, you can see that table2 (person1) has:
2 quantities of "55555"
1 quantity of "33333"
In this scenario, I refer to quantity as unique row
And the table3 (person2) has:
1 quantity of "33333"
1 quantity of "12345"
The total quantities between both persons (table2 and table3) is :
2 quantities of "55555"
2 quantities of "33333"
1 quantity of "12345"
Which is exactly as required by the recipe, so everything is fine, however, if something is missing, the program should return an error.
I really cant get my head around on how to build this query to sum all the serials between the person1 and person2 and compare it to the recipe. Can someone help please?