python - SQL - Select Values From A Table Where A Corresponding Value Matches The Results Of Another Select Statement

Question

Since the title is quite confusing, allow me to clarify. In this instance, I am trying to select all parentemails of year 10 students. However, the year grade of the students are stored in another table, making the select statement rather tricky.

This is my attempt so far, I hope it highlights the roadblock I am at.

conn = sqlite3.connect('test.db')
c = conn.cursor()

# Makes tables
c.execute(
    """
    CREATE TABLE IF NOT EXISTS student (
        year INTEGER,
        code INTEGER,
        PRIMARY KEY (code)
    )
""")

c.execute(
    """
    CREATE TABLE IF NOT EXISTS studentcontact (
        contactcode INTEGER,
        studentcode INTEGER,
        parentemail TEXT,
        PRIMARY KEY (contactcode),
        FOREIGN KEY (studentcode) REFERENCES student(code)
    )
""")

c.execute("""
    INSERT OR REPLACE INTO student (code, year) VALUES 
        (501, 9), 
        (502, 10), 
        (503, 10)
""")

c.execute("""
    INSERT OR REPLACE INTO studentcontact (contactcode, studentcode, parentemail) VALUES
        (401, 501, "[email protected]"),
        (402, 502, "[email protected]"),
        (403, 503, "[email protected]")
""")

### -- QUERY HERE -- ##

# My attempt so far
query = """
    SELECT code FROM student WHERE year ='10'
    SELECT parentemail FROM studentcontact WHERE studentcode = *results from select statement above*
    """

question from:https://stackoverflow.com/questions/65859884/sql-select-values-from-a-table-where-a-corresponding-value-matches-the-results

Answer 1

One way to do this is:

SELECT parentemail
FROM studentcontact
WHERE studentcode IN (
    SELECT code
    FROM student
    WHERE year='10'
)