时间戳

首页 > 解决方案 > 将 2 个表中的列与 Where 子句进行比较

mysql - 将 2 个表中的列与 Where 子句进行比较

问题描述

我有以下语句导致没有结果：

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

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

标签： mysqlwhere

解决方案

您需要执行 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

推荐阅读