Pyodbc SQL SELECT Cursor.execute duplicating results

Question

 Records = Cursor.execute("SELECT cust_addr1, cust_addr2, cust_postcode, cust_telno_home, cust_email \ FROM tblcustomer, tblpet \ WHERE cust_surname = ? and tblpet.pet_deceased = ?" ,(SearchCriteria[0], "Y")).fetchall()

I can run this query if i remove the tblpet perfectly fine but when i add the tblpet the query runs but i get the results dupliated. So instead of 5 results i had something like 6300 but all of the same results looped.

Thanks

EDIT

I have now fixed it thanks to Mureinik and Siyual for telling me about joins. I then found these

https://msdn.microsoft.com/en-us/library/bb243855(v=office.12).aspx

JOINing mdb tables with pyodbc

It worked 50% bu then i had a great idea of going into access and making the query then switching to SQL view and copying it. It works great and i was so so close

ANSWER

 Records = Cursor.execute("""SELECT tblcustomer.cust_addr1, tblcustomer.cust_addr2, tblcustomer.cust_postcode, tblcustomer.cust_telno_home, tblcustomer.cust_email FROM tblcustomer INNER JOIN tblpet ON (tblcustomer.cust_no = tblpet.pet_cust_no) WHERE cust_surname = ? and tblpet.pet_deceased = ?""", (SearchCriteria[0],"Y")).fetchall()

Because this is doing a CROSS JOIN. If you intend to JOIN the tables, use the explicit JOIN syntax (don't use commas in the FROM clause - seriously, this was deprecated in 1992) and show how the tables are related in the ON clause. — Siyual
– Siyual, Commented Oct 27, 2016 at 18:26

Mureinik · Accepted Answer · 2016-10-28 11:38:42Z

When you write an implicit join like you did here (i.e., have more than one table in the from clause), the database creates a Cartesian product of the rows. You need to add a condition to match only the appropriate rows. E.g., assuming the customer has an ID and the pet has the ID of the customer:

SELECT cust_addr1, cust_addr2, cust_postcode, cust_telno_home, cust_email FROM tblcustomer, tblpet WHERE tblcustomer.id = tblpet.customer_id AND -- Here! cust_surname = ? AND tblpet.pet_deceased = ?

Or better yet, you can use the explicit join syntax:

SELECT cust_addr1, cust_addr2, cust_postcode, cust_telno_home, cust_email FROM tblcustomer JOIN tblpet ON tblcustomer.id = tblpet.customer_id -- Here! WHERE cust_surname = ? AND tblpet.pet_deceased = ?

Collectives™ on Stack Overflow

Pyodbc SQL SELECT Cursor.execute duplicating results

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related