MySQL Join

Understand the idea

To keep this example independent of extra schema setup, it joins the products table to itself as two named roles. The equality condition pairs each product with itself, so one row remains per product. Real order-item reports join different tables on their documented keys and must check whether one-to-many matches repeat header totals.

Where to try it

Use a local Python environment with mysql-connector-python and your own separate MySQL lab server. Read the lab setup below before running. This example is not connected to this website’s database and cannot run in the restricted online Python tool.

A worked example

import os
import mysql.connector
connection = mysql.connector.connect(
    host=os.environ.get("DB_HOST", "127.0.0.1"),
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    database=os.environ["DB_NAME"],
)
cursor = connection.cursor()
try:
    cursor.execute("SELECT p.product_id, p.name, reference.stock FROM workshop_products AS p JOIN workshop_products AS reference ON reference.product_id = p.product_id ORDER BY p.product_id")
    for row in cursor:
        print(row)
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

Each product prints once with its matched stock. The aliases make the two table roles explicit.

Try one change

Sketch an order_items table and explain why joining it would change the result to one row per order line.

Local lab setup

Use a local Python virtual environment with mysql-connector-python installed and a separate MySQL lab server. Run the create-database and create-table lessons first, then insert a few lab products. Set DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME in the environment of the process that runs the script. These snippets are complete driver examples; they have not been run against your database.