mysql - 使用具有相同外键的两列

我有一个数据库，它有5个表，但是我有问题。在运货表中，有两列，即源和目的地，它们都是对路由表的外键引用，但是当我从运货表中选择一条记录时，它将只为两者显示相同的路由名。

这是代码：

SELECT fare, commission, driver,shipment._date, routename, vehiclenumber, productname, source, destination, routename,ownername
FROM route, shipment, product, vehicle,owner
WHERE vehicle.vehicleid = shipment.vehicle
AND shipment.source
and vehicle.owner=owner.ownerid
AND shipment.destination = route.routeid
AND shipment.product = product.productid
AND vehicle.vehiclenumber =  'nk-234'
ORDER BY _date
LIMIT 0 , 30

最佳答案

要将一个表（一个货件）中的一条记录连接到另一张表（两条路线）中的多条记录，您最好对JOIN表使用显式的route两次（或更多次，因为无论您碰到多少链接，需要）。

这是对查询的快速修改，以演示用法。请特别注意routename中的两个SELECT列和JOIN中的route表的两个FROM：

SELECT fare,
       commission,
       driver,
       shipment._date,
       RS.routename,    <-- field from first join
       vehiclenumber,
       productname,
       source,
       destination,
       RD.routename,    <-- field from second join
       ownername

FROM shipment
JOIN route RS ON RS.routeID = shipment.source       <-- join 1; source
JOIN route RD ON RD.routeID = shipment.destination, <-- join 2; destination
     product,
     vehicle,
     owner

WHERE vehicle.vehicleid = shipment.vehicle
  AND vehicle.owner = owner.ownerid
  AND shipment.product = product.productid
  AND vehicle.vehiclenumber =  'nk-234'

ORDER BY _date

LIMIT 0 , 30

显然，这可能与您的需求不完全匹配，因为我不知道其他选定字段来自哪个表，以确保它们都被考虑在内。