Join Query

Date: March 12th 2016
Last updated: March 12th 2016

After normalising the database, the data are separated across tables and linked by foreign keys. Getting at this data requires a select statement by joining tables.

This example follows on from the code in foreign keys. That is, the tables and foreign keys are already created. The objective here is to print out the patients name and animal type for all records that are dogs (e.g. 'rover', 'dog').

Code

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# execute: 3 parts
# 1. select the columns to print out (e.g. patient name
# 2. join on 2 columns that match (type INTEGER)
# 3. return only records matching "dog"
cur.execute('SELECT patient.patientname, animals.animaltype FROM patient \
             JOIN animals ON animals.animalid == patient.patientanimal \
             WHERE animals.animaltype = "dog";')

# print out records
rows = cur.fetchall()
for row in rows:
    print(row)

# commit and close
con.commit()
con.close()

Output

('rover', 'dog')
('peanut', 'dog')
('snoopy', 'dog')