How do i join functions within Python MySql program?

Question

So i want to have it so that i have 6 columns. But the 6th is for orders.

The way i see it is VNr is connected to vare and ordrelinje. And thats where OrdreNr comes in which is number of orders, which is what i want.

So i tried write something like this. But any hint on how you would make it work

def DisplayData():
     sqlCon = pymysql.connect(host = "localhost", user = "root", password = "root", database = "varehusdb")
     cur =sqlCon.cursor()

     sql="SELECT VNr, Betegnelse, Pris, KatNr, Antall, OrdreNr FROM vare JOIN ordrelinje ON vare.VNr = ordrelinje.VNr"
     cur.execute(sql)
     result = cur.fetchall()
     if len(result) !=0:
         self.varer_records.delete(*self.varer_records.get_children())
         for row in result:
             self.varer_records.insert('', END, values =row)
         sqlCon.commit()
     sqlCon.close()

When i try to enter this in MySql it works like a charm. But for some reason i get this error when i try to click "Display" button in Python application

"pymysql.err.OperationalError: (1052, "Column 'VNr' in field list is ambiguous") "

Answer 1

sql="SELECT vare.VNr, Betegnelse, Pris, KatNr, Antall, OrdreNr FROM vare JOIN ordrelinje ON vare.VNr = ordrelinje.VNr"

Try to put the table name before the field name

Answer 2

You should always define from which table you are selecting information every time you join.

Simply use this "template":

SELECT d1.[column_name], d2.[column_name] .... 
FROM table1 d1 
JOIN table2 d2 ON d1.smth = d2.smth
JOIN .......

You can always define custom name of the table once you are JOINing it or FROMing it.

This will save you from alot of trouble.