如何通过Python在PostgreSQL中使用单条SQL查询合并存在多对一关联的两张表（同时关联name1与name2字段）

Got it, let's fix this! Since you need to pull in both name1 and name2's corresponding age and area from the personnel table, the trick is to join personnel_tbl twice—once for each name field in order_tbl. Using table aliases lets PostgreSQL tell the two instances of the same table apart, so you can grab the right details for both names.

Final SQL Query #

SELECT 
    order_tbl.sales,
    order_tbl.name1,
    p1.age AS name1_age,
    p1.area AS name1_area,
    order_tbl.name2,
    p2.age AS name2_age,
    p2.area AS name2_area
FROM order_tbl
INNER JOIN personnel_tbl p1 ON order_tbl.name1 = p1.name
INNER JOIN personnel_tbl p2 ON order_tbl.name2 = p2.name;

Breakdown of the Query: #

• We alias personnel_tbl as p1 when joining on name1—this gives us the age and area for the first name in the order.
• We alias it again as p2 when joining on name2—this pulls in the matching details for the second name.
• Using column aliases like name1_age and name2_area keeps your result set clean and avoids ambiguous duplicate column names.

Handling Missing Matches #

If there's a chance name1 or name2 might not exist in personnel_tbl (and you still want to keep those order rows in your results), swap INNER JOIN with LEFT JOIN:

SELECT 
    order_tbl.sales,
    order_tbl.name1,
    p1.age AS name1_age,
    p1.area AS name1_area,
    order_tbl.name2,
    p2.age AS name2_age,
    p2.area AS name2_area
FROM order_tbl
LEFT JOIN personnel_tbl p1 ON order_tbl.name1 = p1.name
LEFT JOIN personnel_tbl p2 ON order_tbl.name2 = p2.name;

This will return NULL for the age/area fields where there's no matching name in personnel_tbl, instead of filtering out the entire order row.

Example Python Implementation (using psycopg2) #

Here's how you'd run this query in Python with the popular psycopg2 library:

import psycopg2

# Set up your database connection
conn = psycopg2.connect(
    dbname="your_database_name",
    user="your_username",
    password="your_password",
    host="your_host"
)

# Create a cursor to execute queries
cur = conn.cursor()

# Run the join query
query = """
SELECT 
    order_tbl.sales,
    order_tbl.name1,
    p1.age AS name1_age,
    p1.area AS name1_area,
    order_tbl.name2,
    p2.age AS name2_age,
    p2.area AS name2_area
FROM order_tbl
INNER JOIN personnel_tbl p1 ON order_tbl.name1 = p1.name
INNER JOIN personnel_tbl p2 ON order_tbl.name2 = p2.name;
"""
cur.execute(query)

# Fetch and process results
results = cur.fetchall()
for row in results:
    print(f"Sales: {row[0]} | Name1: {row[1]} (Age: {row[2]}, Area: {row[3]}) | Name2: {row[4]} (Age: {row[5]}, Area: {row[6]})")

# Clean up connections
cur.close()
conn.close()

内容的提问来源于stack exchange，提问作者Ihmon