mysql - 使用 Where 子句比较 2 个表中的列

Question

我有以下语句，但没有结果:

SELECT 
    temp_manual_pickups.PO,
    temp_manual_pickups.INVOICE,
    elite_routes.SOURCE,
    elite_routes.SOURCE_UDID,
    temp_manual_pickups.DESTINATION,
    temp_manual_pickups.DESTINATION_UDID,
    temp_manual_pickups.DESTINATION_ADDRESS,
    temp_manual_pickups.DESTINATION_CITY,
    temp_manual_pickups.DESTINATION_STATE,
    temp_manual_pickups.DESTINATION_ZIP,
    elite_routes.SOURCE AS 'ORDER_BY',
    temp_manual_pickups.ZONE,
    temp_manual_pickups.PART_NUMBER,
    temp_manual_pickups.PART_DESCRIPTION,
    temp_manual_pickups.SHIP_TO,
    temp_manual_pickups.SHIP_TO_ADDRESS,
    temp_manual_pickups.SHIP_TO_CITY,
    temp_manual_pickups.SHIP_TO_STATE,
    temp_manual_pickups.SHIP_TO_ZIP
FROM
    temp_manual_pickups,
    elite_routes
WHERE
    temp_manual_pickups.zone = elite_routes.route;

如果我运行以下 2 条语句:

SELECT * FROM elite_routes
WHERE ROUTE = 4

结果

route, source, source_udid
4   FBP Doylestown - Main   C-Warehouse

和

SELECT * FROM TEMP_MANUAL_PICKUPS
where zone = 4

结果

PO, INVOICE, DESTINATION, DESTINATION_UDID, DESTINATION_ADDRESS, DESTINATION_CITY, DESTINATION_STATE, DESTINATION_ZIP, ZONE, PART_NUMBER, PART_DESCRIPTION, ship_to, ship_to_address, ship_to_city, ship_to_state, ship_to_zip
NEW RETURN-G    PU-203151   MAGARITY AUTO   Z3422285    7972 ROCKWELL AVE   PHILADELPHIA    PA  19111-2223  4
    68286732AA  BRACKET-RADIATOR SUPPORT                    
NEW RETURN-G    PU-203151   MAGARITY AUTO   Z3422285    7972 ROCKWELL AVE   PHILADELPHIA    PA  19111-2223  4
    68288334AA  BRACKET-SUPPORT FRONT                   
NEW RETURN-G    PU-203151   MAGARITY AUTO   Z3422285    7972 ROCKWELL AVE   PHILADELPHIA    PA  19111-2223  4
    68225214AA  CLIP-FASCIA

当我单独运行这两个语句时，很明显两个表的相应列中都有“4”，但是当我在 where 子句中比较它们时，我没有得到任何结果。我在某个地方有语法错误吗？

Answer 1

您需要执行 JOIN 来执行连接多个表中数据的查询。

这是带有左连接的查询:

SELECT 
    temp_manual_pickups.PO,
    temp_manual_pickups.INVOICE,
    elite_routes.SOURCE,
    elite_routes.SOURCE_UDID,
    temp_manual_pickups.DESTINATION,
    temp_manual_pickups.DESTINATION_UDID,
    temp_manual_pickups.DESTINATION_ADDRESS,
    temp_manual_pickups.DESTINATION_CITY,
    temp_manual_pickups.DESTINATION_STATE,
    temp_manual_pickups.DESTINATION_ZIP,
    elite_routes.SOURCE AS 'ORDER_BY',
    temp_manual_pickups.ZONE,
    temp_manual_pickups.PART_NUMBER,
    temp_manual_pickups.PART_DESCRIPTION,
    temp_manual_pickups.SHIP_TO,
    temp_manual_pickups.SHIP_TO_ADDRESS,
    temp_manual_pickups.SHIP_TO_CITY,
    temp_manual_pickups.SHIP_TO_STATE,
    temp_manual_pickups.SHIP_TO_ZIP
FROM
    temp_manual_pickups
LEFT JOIN 
    elite_routes
ON 
    temp_manual_pickups.zone = elite_routes.route;

一些文档:https://www.w3schools.com/sql/sql_join_left.asp