MYSQL comparison between 3 tables help

Thread Starter

zazas321

Joined Nov 29, 2015
850
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?
 

Thread Starter

zazas321

Joined Nov 29, 2015
850
Do you suggest that I read all 3 tables and store all the values as variables and then do a comparison? I believe that is one way of doing, but I dont think this would be an efficient way of doing (having to store all the variables from all 3 tables) and then do a comparison while I can just do a comparison within the query and not needing to store any variables
 

Yaakov

Joined Jan 27, 2019
2,929
Do you suggest that I read all 3 tables and store all the values as variables and then do a comparison? I believe that is one way of doing, but I dont think this would be an efficient way of doing (having to store all the variables from all 3 tables) and then do a comparison while I can just do a comparison within the query and not needing to store any variables
No, I don’t suggest you read the entire table into an array. You can do a query that returns only the relevant records then compare them using Python. Instead of dealing with SQL, you can make a stored procedure and use Python’s flexibilty.

Check out stored procedures in MySQL.
 

Thread Starter

zazas321

Joined Nov 29, 2015
850
Yes I think you are right. I have read about the stored procedures and it seems that it could be what I am looking for, however, I would like to share with the results that I have got so fast as I think I am very close to achieving what I need.

The mysql :
https://www.db-fiddle.com/f/8eSUQCwM3NwNDSHJmATycp/7

The first query returns me data of table 1 which consits what serial numbers and quantity of each I need. Knowing that, I can make a nested for loop and query table2 and table3 with each serial and check the sum of count(*) for that particual serial number. It seemed to work when I tested in db-fiddle (Run the querry and see the last two results)

However, I am not able to reproduce the same thing in my python program:

Code:
sql = "SELECT Serial, count(*) as NUM FROM {table} GROUP BY Serial"
    cur.execute(sql.format(table=table_name))
    for Serial,number in cur.fetchall() :
        print("Serial need to take=",Serial)
        print("Number of that serial =",number)
        sql2 = "SELECT SUM(total_count) AS total_count FROM(SELECT count(*) AS total_count FROM Raspberry1_data2 WHERE Serial = %s UNION ALL SELECT count(*) FROM Raspberry2_data2 WHERE Serial = %s)AS a"
        result = cur.execute(sql2,Serial,Serial)#sql2 = "SELECT Serial,count(*) FROM Raspberry1_data2 WHERE Serial=%s UNION ALL SELECT Serial,count(*) FROM Raspberry2_data2 WHERE Serial=%s"
        print("result asnwer=",result)
Something is not right with my second querry since It returns result in a weird format:
Code:
Serial need to take= an000002
Number of that serial = 1
result asnwer= <generator object MySQLCursor._execute_iter at 0xb06a3030>
Serial need to take= at000003
Number of that serial = 2
result asnwer= <generator object MySQLCursor._execute_iter at 0xb06a30b0>
Serial need to take= ps000001
Number of that serial = 2
result asnwer= <generator object MySQLCursor._execute_iter at 0xb06a3030>
It should return a sum of count(*) from both tables for a specific serial number which I pass as a variable (%s)

I have also tried to use fetchall():
Code:
    sql = "SELECT Serial, count(*) as NUM FROM {table} GROUP BY Serial"
    cur.execute(sql.format(table=table_name))
    for Serial,number in cur.fetchall() :
        print("Serial need to take=",Serial)
        print("Number of that serial =",number)
        sql2 = "SELECT SUM(total_count) AS total_count FROM(SELECT count(*) AS total_count FROM Raspberry1_data2 WHERE Serial = %s UNION ALL SELECT count(*) AS total_count FROM Raspberry2_data2 WHERE Serial = %s)AS total_count"
        cur.execute(sql2,Serial,Serial)
        for total_count in cur.fetchall():
            print("result asnwer=",total_count)
And the result is as following:
Code:
mysql.connector.errors.InterfaceError: No result set to fetch from.
 
Last edited:

Yaakov

Joined Jan 27, 2019
2,929
I'll leave this for someone who is more conversant in the connection between MySQL and Python. I can't really help much with that, I haven't used the two together.
 

MrSalts

Joined Apr 2, 2020
248
Your problem would greatly simplified by setting up Table 2 with one more column of "person number". Then you can eliminate table 3. And you can simply specific if each inventory item belongs to person1 or person2 and do queries off of table 2 for all inventory quantities.
 

Thread Starter

zazas321

Joined Nov 29, 2015
850
I understand what you meant but I do not know if that is good idea for my particular task. What happens is:
PART1 of task:
A person is scanning a barcode of an item which then makes an API request to the server which has all the information for this particular item. I then create a table1 and store that information there.
PART2 of task:
I compare table2 and table3 with what I just read from table1 and determine whether I can proceed with a task or not. I save the data in table1 because It can be further split but it would be quite complex to explain .
After a task is completed, I delete table1 and wait for person to scan another item
 

MrSalts

Joined Apr 2, 2020
248
I think you need to better understand the various "states" your inventory can have.
Also, you don't need to create and delete tables - you shouldn't create and delete tables Ideally, you shouldn't delete anything. You are essentially making an ERP system (at least the inventory part of an ERP)

ideally, you should add more columns to your inventory table to show the status of each lot number of each ingredient. This is like the material movement table in SAP.

also, you are using a table like a variable It should be a list of thousands or hundreds of thousands of transactions.

your material 55555 quantities should be tracked and traced by manufacturer, manufacturer location and lot number, quantity and status.

status can be; and each status change should have a time stamp as it is on
- pending quality chack
- available for use
- promised to (specific batch usage) & quantity promised
- transferred out (actually sent)
- returned to inventory (cancelled usage)
- unpromised (cancelled planned usage)
Then you can run queries against that table to constantly show you how much is available, from where - or it can show you want has been used and how .
 

BobaMosfet

Joined Jul 1, 2009
1,663
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?
Here:

SAM'S Tach Yourself SQL in 10 Minutes
Author: Sam's Publishing
ISBIN-10: 0-672-31664-1

Is this a homework assignment?
 

MrSalts

Joined Apr 2, 2020
248
Unfortunately, "Teaching yourself sql in 10 minutes" books never go into the strategy of setting up a relational database, setting up tables without duplicate data pairs while minimizing search effort (time). From my point of view, the OP is well deeper than 10-minutes into his know how of sql Now the OP needs some mentoring
 

ApacheKid

Joined Jan 12, 2015
385
There are some problems here, the biggest is the fact there's a dedicated table for each "person".

Rather than two tables (table2 and table3) you can have a single table with an additional column - PersonID.

This means you then have just two tables, no matter how many "people" you need, just two tables will represent the data.

Then with that done, you can write a better query, perhaps using a JOIN on Serial to get what you want, bear in mind too that SQL supports a SUM( ) operation as well.

If the requirement insists you do this with one table per person then the problem can still be solved, but check that, is it absolutely required that way?
 

Thread Starter

zazas321

Joined Nov 29, 2015
850
There are some problems here, the biggest is the fact there's a dedicated table for each "person".

Rather than two tables (table2 and table3) you can have a single table with an additional column - PersonID.

This means you then have just two tables, no matter how many "people" you need, just two tables will represent the data.

Then with that done, you can write a better query, perhaps using a JOIN on Serial to get what you want, bear in mind too that SQL supports a SUM( ) operation as well.

If the requirement insists you do this with one table per person then the problem can still be solved, but check that, is it absolutely required that way?

First of all, thanks all for the tips. I will explain more about this project. This is not a online shop database or something. If you have heard about "Pick to Light" systems that is what it is. This is a system designed to help warehouse operators to pack the correct things.


A responsible person generates a recipe and puts it in the server. When an operators scans a barcode on an emtpy box, my system makes an API request with that code to the server and I get what items warehouse operator need to collect for this particular box. With that data, I create a table1 and store that data there. I will probably be keeping all the tables that I create just for the record. At the end of the day I will be able to look how many boxes has been packed . All items have unique serial number therefore it makes sense to sort them based on that which is what I am doing.

Little bit more about the system... :

There are 2 warehouse operators shoulder to shoulder that are working on this system. Hence 2 seperate systems. The core of the system is Raspberry PI. Both warehouse operators have their own "working area" which is the reason why I have decided to create a mysql table for each operator.

table2 and table3 just represents what items are available on the person's "working area". If a person1 cannot fully complete the particular box - no problem, leave 1 or 2 items out, and the person2 will finish it (Thats where my initial question came from, I must check whether the box can be completed between both people )

person1 always starts packing items, person2 always finishes whatever person1 has not put in a box. If there is an item which is a part of recipe is not in person working area, person1 will be required to take that item otherwise he would not be able to continue.

Hopefully that is more clear, I should have explained that at the beggining.


And yes, you are right, 2 individual tables are probably not required, I just thought that it will be much easier to keep track what items each working place has assigned to.
 

Thread Starter

zazas321

Joined Nov 29, 2015
850
I have put everything in one table as you have suggested and made some progress with my MYSQL. I am learning more and more about it.
Check this out:
https://www.db-fiddle.com/f/8eSUQCwM3NwNDSHJmATycp/25

As you can see, the last query is able to find the number of serial matches in a table. However, I copied the exact same query in my python and it complains about syntax:

Code:
    sql = "SELECT Serial, count(*) as NUM FROM {table} GROUP BY Serial"
    cur.execute(sql.format(table=table_name))
    for Serial,number in cur.fetchall() :
        print("Serial need to take=",Serial)
        print("Number of that serial need to take =",number)
        sql2 = "SELECT count(*) FROM Raspberry_data WHERE Serial=%s"
        cur.execute(sql2,Serial)

        for answer, in cur.fetchall():
            print("Number of serial existing between both =",answer)
            if(number > answer):
                print("cannot finish this code, missing item=",Serial)
                string = "cannot finish this code, missing item="+Serial
                label_RED.config(text=string)
                master.after(5000,lambda : app.refresh_app2())
                return
The new query is :
sql2 = "SELECT count(*) FROM Raspberry_data WHERE Serial=%s"
cur.execute(sql2,Serial)

I dont understand why it complains?
Code:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1
 
Last edited:

Thread Starter

zazas321

Joined Nov 29, 2015
850
I have figured out the issue with my python query. When you use variables inside mysql query, you must put them as a tuple:

FOLLOWING IS NOT CORRECT
Code:
       sql2 = "SELECT count(*) FROM Raspberry_data WHERE Serial=%s"
        cur.execute(sql2,Serial)
FOLLOWING IS CORRECT
Code:
       sql2 = "SELECT count(*) FROM Raspberry_data WHERE Serial=%s"
        cur.execute(sql2, (Serial,) )
 
Top