如何通过Relations中间关联表查询两个关联表的数据？

Got it, so you need to pull data from the Buyers table and their corresponding sellers (I assume there's a Sellers table with a matching structure to Buyers, since your Relations table uses sellerID as a foreign key) using the Relations junction table. Here's how to do it with SQL joins:

Basic Inner Join for Matched Pairs #

This query returns only rows where there’s a valid match in all three tables—so it will exclude any Relations entries where the buyer or seller doesn’t exist in their respective tables:

SELECT 
    b.ID AS buyer_id,
    b.name AS buyer_name,
    b.phone AS buyer_phone,
    s.ID AS seller_id,
    s.name AS seller_name,
    s.phone AS seller_phone
FROM Relations r
INNER JOIN Buyers b ON r.buyerID = b.ID
INNER JOIN Sellers s ON r.sellerID = s.ID;

Using your sample Buyers data, this will only return the first three rows from Relations (since buyers 4 and 5 aren’t present in your Buyers table).

Left Join to Include All Relation Entries #

If you want to see every entry in Relations—even if the buyer or seller is missing from their table (though foreign keys should prevent this scenario)—use a LEFT JOIN with COALESCE to handle missing values gracefully:

SELECT 
    r.buyerID,
    COALESCE(b.name, 'No buyer found') AS buyer_name,
    COALESCE(b.phone, 'N/A') AS buyer_phone,
    r.sellerID,
    COALESCE(s.name, 'No seller found') AS seller_name,
    COALESCE(s.phone, 'N/A') AS seller_phone
FROM Relations r
LEFT JOIN Buyers b ON r.buyerID = b.ID
LEFT JOIN Sellers s ON r.sellerID = s.ID;

This will include the Relations rows for buyerID 4 and 5, labeling them as "No buyer found" since those IDs don’t exist in your sample Buyers data.

Key Takeaways #

• INNER JOIN: Use this when you only want records that have valid matches across all three tables.
• LEFT JOIN: Use this when you need to retain every entry from the junction table (Relations), even if some related buyer/seller data is missing.
• Always join tables on their foreign key-primary key pairs (r.buyerID = b.ID and r.sellerID = s.ID) to ensure accurate relationships between your data.

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