Mysql结果来自ids不匹配的多个表

Question

你好，我有一个有5个表的数据库:用户，驱动器，客户端，司机，车辆。

我正试着用相应的汽车司机和客户去取所有的驱动器。我提出了以下查询：

SELECT drives.id,
drives.driver AS driver_id,
CONCAT(LEFT(drivers.name, 1), '. ', drivers.surname) AS driver_name,
drives.client AS client_id,
CONCAT(LEFT(clients.name, 1), '. ', clients.surname) AS client_name,
drives.vehicle AS vehicle_id,
vehicles.license_plates AS license_plates,
drives.departure,
drives.destination,
drives.distance,
drives.type,
drives.payment_type,
drives.timestamp,
drives.total,
drives.expenses,
drives.profit,
CASE
    WHEN DATE(drives.timestamp) < DATE(NOW()) AND drives.total > 0 THEN 'Completed'
    WHEN DATE(drives.timestamp) < DATE(NOW()) AND drives.total = 0 THEN 'Overdue'
    WHEN DATE(drives.timestamp) >= DATE(NOW()) THEN
        CASE
            WHEN drives.total = 0 THEN 'Pending'
            WHEN drives.total > 0 THEN 'Prepaid'
        END     
END AS payment_status,
DATE_FORMAT(drives.timestamp, '%d-%m-%Y %H:%i:%s') AS 'stamp'
FROM drives, clients, drivers, vehicles WHERE 
drives.driver = drivers.id AND
drives.client = clients.id AND
drives.vehicle = vehicles.id AND
drives.user = '146' ORDER BY id ASC LIMIT 9999999999 OFFSET 0

但是，如果我从vehicles表中删除车辆记录，然后尝试从表驱动器中获取所有驱动器记录，则不会打印出具有drives.vehicle = vehicle.id (不再存在)的驱动器记录。

你可以理解这不是我想要的。我希望所有的驱动器记录被打印出来，即使一个车辆，客户，司机被删除。

驱动器表的数据示例：

id  timestamp               user    driver  client  vehicle departure   destination distance    type        payment_type    total   expenses    profit  note
1   2013-02-14 10:33:26     146     1       1       1       Address 1   Address 2   0           Deprature   Cash            0       0           0       hello world

Answer 1

您目前正在使用内部连接，请考虑使用外部连接。

...
FROM drives 
LEFT OUTER JOIN clients ON drives.client = clients.id
LEFT OUTER JOIN drivers  ON drives.driver = drivers.id
LEFT OUTER JOIN vehicles ON drives.vehicle = vehicles.id
WHERE
.... 

Answer 2

您应该在这里使用联接，这肯定有助于捕获空值，LEFT JOIN应该会有所帮助。

SELECT DRI.id, DRI.driver AS driver_id, CONCAT(LEFT(DI.name, 1), '. ', DI.surname) AS driver_name,
DRI.client AS client_id, CONCAT(LEFT(CI.name, 1), '. ', CI.surname) AS client_name,
DRI.vehicle AS vehicle_id, vehicles.license_plates AS license_plates, DRI.departure,
DRI.destination, DRI.distance, DRI.type, DRI.payment_type, DRI.timestamp, DRI.total,
DRI.expenses, DRI.profit,
CASE
    WHEN DATE(DRI.timestamp) < DATE(NOW()) AND DRI.total > 0 THEN 'Completed'
    WHEN DATE(DRI.timestamp) < DATE(NOW()) AND DRI.total = 0 THEN 'Overdue'
    WHEN DATE(DRI.timestamp) >= DATE(NOW()) THEN
        CASE
            WHEN DRI.total = 0 THEN 'Pending'
            WHEN DRI.total > 0 THEN 'Prepaid'
        END     
END AS payment_status,
DATE_FORMAT(DRI.timestamp, '%d-%m-%Y %H:%i:%s') AS 'stamp'
FROM drives DRI
LEFT JOIN clients CI ON DRI.client = CI.id
LEFT JOIN drivers DI ON DRI.driver = DI.id,
LEFT JOIN vehicles VI ON DRI.vehicle = VI.id
WHERE
DRI.user = '146'
ORDER BY DRI.id ASC