mysql - 错误代码 : 1054. 'vehicle_details.batch_id' 中的未知列 'on clause'

Question

运行以下 MySQL 查询时，我收到错误代码:1054。“on 子句”中的未知列“vehicle_details.batch_id”错误。

有人可以帮忙吗？

谢谢

约翰

SELECT 
    supplier,
    vehicle_details.status,
    vehicle_details.batch_id,
    veh_id,
    fund_type,
    suppliers.supplier_name,
    reg_no,
    car_make.car_make,
    car_model,
    creation_date,
    stock_plan,
    money_received,
    hpi_registered,
    invoice_checked,
    details_checked,
    confirmation_received,
    log_mot_check
FROM
    vehicle_details,
    suppliers,
    car_make
LEFT JOIN batch
ON vehicle_details.batch_id = batch.batch_id
WHERE
    vehicle_details.supplier = suppliers.supplier_id
        AND vehicle_details.car_make = car_make.car_id
        AND vehicle_details.status like 's'
        AND vehicle_details.batch_id like '%'
        AND veh_id like '%'
        AND fund_type like '%'
        AND supplier like '%'
        AND reg_no like '%'
        AND car_id like '%'
        AND car_model like '%'
        AND creation_date like '%'
        AND stock_plan like '%'
        AND money_received like '%'
ORDER BY vehicle_details.batch_id DESC

Answer 1

仅使用显式连接语法

SELECT 
    supplier,
    vehicle_details.status,
    vehicle_details.batch_id,
    veh_id,
    fund_type,
    suppliers.supplier_name,
    reg_no,
    car_make.car_make,
    car_model,
    creation_date,
    stock_plan,
    money_received,
    hpi_registered,
    invoice_checked,
    details_checked,
    confirmation_received,
    log_mot_check
FROM
    vehicle_details
INNER JOIN suppliers ON vehicle_details.supplier = suppliers.supplier_id
INNER JOIN car_make ON vehicle_details.car_make = car_make.car_id
LEFT JOIN batch ON vehicle_details.batch_id = batch.batch_id
WHERE   vehicle_details.status like 's'
        AND (vehicle_details.batch_id is null or vehicle_details.batch_id like '%')
        AND veh_id like '%'
        AND fund_type like '%'
        AND supplier like '%'
        AND reg_no like '%'
        AND car_id like '%'
        AND car_model like '%'
        AND creation_date like '%'
        AND stock_plan like '%'
        AND money_received like '%'
ORDER BY vehicle_details.batch_id DESC

你在这里隐式加入

FROM
vehicle_details,
suppliers,
car_make

并且有明确的

LEFT JOIN batch ON vehicle_details.batch_id = batch.batch_id