Query many to many relationships
Date: March 15th 2016
Last updated: March 15th 2016
Accessing records involved in a many to many relationship requires an inner join on all reference tables. The example I use here refers to the tables and data created previously in many_to_many_relationship.
The objective of this query is to sum the cost of vet_visits for each animal and combine those totals with their owners. Some things to note in the database schema and the following query:
1) the vet_visit table holds a column that references the patient table so my first execute command is to join the patient table.
2) the ownerpatient table references the patient id and owner id (this is a table required for the many to many relationship).
3) after an inner join is created for the "many to many reference table", the owner table can be accessed. I have joined the table so I could access the owner names.
Query many to many relationship
import sqlite3
# connect
con = sqlite3.connect('gitbook.db')
# cursor
cur = con.cursor()
# execute (owner, pet, sum(cost))
cur.execute('SELECT SUM(vet_visit.visitprice), \
patient.patientname, \
owner.ownername \
\
FROM vet_visit \
\
INNER JOIN patient \
ON patient.patientid == vet_visit.visitpatientid \
\
INNER JOIN ownerpatient \
ON ownerpatient.patient_id == vet_visit.visitpatientid \
\
INNER JOIN owner \
ON owner.ownerid == ownerpatient.owner_id \
\
GROUP BY owner.ownername, patient.patientname;')
rows = cur.fetchall()
for row in rows:
print(row)
# commit and close
con.commit()
con.close()
Output
Note the values are repeated for each owner.
(506.0, 'peanut', 'ray')
(231.0, 'rover', 'ray')
(86.0, 'tinkles', 'ray')
(231.0, 'rover', 'richard')
(506.0, 'peanut', 'suzy')
(231.0, 'rover', 'suzy')
Useful resources