使用不同的值两次连接同一个表

Question

我正在尝试使用 ID 将“transfers.pickup_areas_group_id”和“transfers.drop_areas_group_id”的值替换为表“areas_group”中的值

我正在使用这个查询：

SELECT 

transfers.id AS transfer_id, 

transfers.name AS transfer_name,  

transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id,

transfers.drop_areas_group_id AS transfer_drop_areas_group_id, 

transfers_pricing.vehicle_id AS vehicle_id, 

transfers_pricing.date_start AS date_start, 

transfers_pricing.date_end AS date_end, 

transfers_pricing.price AS price 

FROM transfers

INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id

我尝试了一个额外的 INNER JOIN 来替换第一个值“transfers.pickup_areas_group_id”，但我找不到替换第二个值“transfers.drop_areas_group_id”的方法

我试过这个查询：

SELECT 

transfers.id AS transfer_id, 

transfers.name AS transfer_name,  

transfers.pickup_areas_group_id AS transfer_pickup_areas_group_id,

areas_group.area_id AS pickup_area_ids,

transfers.drop_areas_group_id AS transfer_drop_areas_group_id, 

transfers_pricing.vehicle_id AS vehicle_id, 

transfers_pricing.date_start AS date_start, 

transfers_pricing.date_end AS date_end, 

transfers_pricing.price AS price 

FROM transfers

INNER JOIN transfers_pricing ON transfers_pricing.transfer_id = transfers.id

INNER JOIN areas_group ON areas_group.id = transfers.pickup_areas_group_id

谢谢，

Answer 1

基本上，您需要另一个加入areas_group；要消除对同一个表的两个引用的歧义，您需要使用表别名。实际上，对在查询中起作用的所有表使用表别名是一个很好的做法：这使得查询更易于读写。SELECT     t.id AS transfer_id,     t.name AS transfer_name,      t.pickup_areas_group_id AS transfer_pickup_areas_group_id,    ag1.area_id AS pickup_area_ids,    t.drop_areas_group_id AS transfer_drop_areas_group_id,     ag2.area_id AS drop_area_ids    tp.vehicle_id AS vehicle_id,     tp.date_start AS date_start,     tp.date_end AS date_end,     tp.price AS price FROM transfers tINNER JOIN transfers_pricing tp ON tp.transfer_id = t.idINNER JOIN areas_group ag1 ON ag1.id = t.pickup_areas_group_idINNER JOIN areas_group ag2 ON ag2.id = t.drop_areas_group_id