如何编写SQL查询获取table_1关联的两个table_3对应cost值

需求说明 #

从table_1中取出id，同时获取ref_1_id和ref_2_id分别通过table_2关联到table_3的cost值，结果集格式为(table_1_id, cost_1, cost_2)。

实现SQL语句 #

SELECT 
    t1.id AS table_1_id,
    t3_1.cost AS cost_1,
    t3_2.cost AS cost_2
FROM 
    table_1 t1
LEFT JOIN 
    table_2 t2_1 ON t1.ref_1_id = t2_1.id
LEFT JOIN 
    table_3 t3_1 ON t2_1.ref_3_id = t3_1.id
LEFT JOIN 
    table_2 t2_2 ON t1.ref_2_id = t2_2.id
LEFT JOIN 
    table_3 t3_2 ON t2_2.ref_3_id = t3_2.id;

逻辑说明 #

• 采用LEFT JOIN而非INNER JOIN，确保即使ref_1_id或ref_2_id无对应关联数据时，table_1的记录仍会被保留，对应cost值显示为NULL。
• 对关联的table_2和table_3分别设置别名t2_1/t3_1、t2_2/t3_2，区分两条关联路径，避免字段冲突。

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