如何使用SQL连接具有间接关联关系的数据条目？

Absolutely, JOINs are the perfect tool for this scenario! Your goal is to translate the node IDs in the Edges table into their corresponding names from the Nodes table—and since each edge connects two nodes, you'll need to join the Nodes table twice to get both the source and target names.

Step-by-Step Explanation #

1. First, join the Edges table to the Nodes table on the source node ID (the first column in your Edges table) to pull the name of the starting node.
2. Then, join the Edges table to the Nodes table a second time, this time matching on the target node ID (the second column in your Edges table) to get the name of the ending node.

Example SQL Query #

Assuming your tables follow this schema (aligned with your sample data):

• nodes: columns node_id (e.g., A, B, C, D) and name (e.g., Joe, Alice)
• edges: columns source (first node ID) and target (second node ID)

Here's the query that will produce your desired result:

SELECT 
    n1.name AS source_name,
    n2.name AS target_name
FROM edges e
INNER JOIN nodes n1 ON e.source = n1.node_id
INNER JOIN nodes n2 ON e.target = n2.node_id;

Testing with Your Sample Data #

When you run this query against your provided data:

• Nodes table: A→Joe, B→Alice, C→Bob, D→Jane
• Edges table: A→B, B→D, C→B

The output will be exactly what you're looking for:

source_name | target_name
------------|------------
Joe         | Alice
Alice       | Jane
Bob         | Alice

Optional: Handling Missing Nodes #

If there's a chance an edge references a node ID that doesn't exist in the Nodes table, you can swap INNER JOIN with LEFT JOIN to retain those edges (the missing name will show as NULL). But for your example, INNER JOIN works perfectly since all edge IDs have matching nodes.

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