从一个带有2个表的SELECT sql查询检索2行PHP

Question

QUOTES TABLE
-----------------
QUOTE_ID_PK  PICKUP_FROM_ID  DROPOFF_TO_ID
1               4                     3
2               2                     3
3               1                     1
4               1                     2
5               3                     2


TOWN TABLE
---------------
 TOWN_ID_PK  DISTANCE    NAME
    1        34          South Central 
    2        43          airport
    3        99          bus station
    4        66          train station

我有两张表来计算一个简单的距离公式。

我需要在orders表中为ORDER_ID返回2行(值为distance)。

我得到了order_id =2，pickupfromdistance=43，dropoffto=99的结果。

我已经尝试了这个网站上的一些想法，这是我的想法，几乎可以工作，但没有返回第二个距离值。我是不是走错路了？我是在为一个简单的问题做一个复杂的解决方案吗？

SELECT town.town_id_pk AS townID, town.distance AS distance, town.name AS townName, town.location AS location, quotes.pickupfrom_id, quotes.dropoffto_id 
FROM town, quotes 
WHERE quotes.pickupfrom_id=town.town_id_pk 
AND quotes.quote_id_pk = '140419420'
UNION ALL
SELECT town.town_id_pk AS townID, town.distance AS distance, town.name AS townName, town.location AS location, quotes.pickupfrom_id, quotes.dropoffto_id
FROM town, quotes 
WHERE quotes.dropoffto_id=town.town_id_pk 
AND quotes.quote_id_pk = '140419420'";

例如，我正在选择：

QUOTE_ID_PK=2, wh

(它有PICKUP_FROM_ID=2和DROPOFF_TO_ID=3)

我想要的结果应该是这样的：

TOWN_ID_PK=2, DISTANCE=43, NAME=AIRPORT

那么下一行将是

TOWN_ID_PK=3, DISTANCE=99, NAME= BUS STATION

一旦我知道哪个距离更远，我就可以继续编写代码来计算每个klm的费率等(我可以自己解决这一部分)。

Answer 1

给个提示：

SELECT 
  ORDER_ID, 
  PICKUP_FROM_TOWN_ID, 
  (SELECT DISTANCE FROM TOWN  where TOWN_ID_PK = quotes.DROPOFF_TO_ID) as DISTANCE, 
  quotes.DROPOFF_TO_ID  
from town, quotes 
WHERE quotes.PICKUP_FROM_TOWN_ID=town.TOWN_ID_PK
AND ORDER_ID = '140419420'

这是一个sqlfiddle

编辑：

要获得两行，只需尝试以下命令：

SELECT 
  ORDER_ID, 
  PICKUP_FROM_TOWN_ID, 
  DISTANCE,
  DROPOFF_TO_ID  
from town, quotes 
WHERE 
    ( quotes.PICKUP_FROM_TOWN_ID=town.TOWN_ID_PK or quotes.DROPOFF_TO_ID=town.TOWN_ID_PK )
AND ORDER_ID = 2

新的sqlfiddle

Answer 2

你只是想知道这两个城镇的距离吗？如果是这样的话，您可以使用join执行此操作

select oq.*, tfrom.distance as distance_from, tto.distance as to_distance
from OrderQuotes oq left outer join
     Towns tfrom
     on oq.PICKUP_FROM_TOWN_ID = tfrom.town_id_pk left outer join
     Towns tto
     on oq.PICKUP_TO_TOWN_ID = tto.town_id_pk;