mysql - 查询中的内连接不起作用

Question

我有一个相当大的查询，它的 express 部分使它不起作用我不确定为什么，没有出现错误，但也许我没有看到一些东西......

SELECT o.purchaseNo, o.dateCreated, c.Name, m.mouldName, cr.courierName
FROM products AS p
INNER JOIN orderedProducts AS op ON op.productID = p.productID
INNER JOIN orders AS o ON op.orderID = o.orderID
INNER JOIN Couriers AS cr ON cr.couriersID = o.couriersID
INNER JOIN customers AS c ON c.customerID = o.customerID
INNER JOIN Moulds AS m ON m.mouldID = p.mouldID
WHERE c.Name LIKE  '%john%'
OR p.name LIKE  '%john%'
OR c.Name LIKE  '%john%'
OR c.POC1 LIKE  '%john%'
OR c.POC2 LIKE  '%john%'
OR c.Address1 LIKE  '%john%'
OR c.Address2 LIKE  '%john%'
OR c.Suburb LIKE  '%john%'
OR c.State LIKE  '%john%'
OR c.Phone LIKE  '%john%'
OR c.Email LIKE  '%john%'
OR c.ABN LIKE  '%john%'
OR c.Fax LIKE  '%john%'
OR c.CompanyName LIKE  '%john%'
OR o.purchaseNo LIKE  '%john%'
OR o.dateCreated LIKE  '%john%'
OR cr.courierName like '%john%'

Couriers cr 是造成问题的线路。如果我将它与任何对 cr 的引用一起删除，它就可以正常工作。否则就不会。

有什么想法可能会导致这种情况吗？

我基本上是在创建一个搜索查询。检查数据库中的许多表。如果您对我如何做得更好有任何建议，那就太好了:)

Answer 1

我猜你需要在 express 公司上使用 LEFT OUTER JOIN。

SELECT o.purchaseNo, o.dateCreated, c.Name, m.mouldName, cr.courierName
FROM products AS p
INNER JOIN orderedProducts AS op ON op.productID = p.productID
INNER JOIN orders AS o ON op.orderID = o.orderID
INNER JOIN customers AS c ON c.customerID = o.customerID
INNER JOIN Moulds AS m ON m.mouldID = p.mouldID
LEFT OUTER JOIN Couriers AS cr ON cr.couriersID = o.couriersID
WHERE c.Name LIKE  '%john%'
OR p.name LIKE  '%john%'
OR c.Name LIKE  '%john%'
OR c.POC1 LIKE  '%john%'
OR c.POC2 LIKE  '%john%'
OR c.Address1 LIKE  '%john%'
OR c.Address2 LIKE  '%john%'
OR c.Suburb LIKE  '%john%'
OR c.State LIKE  '%john%'
OR c.Phone LIKE  '%john%'
OR c.Email LIKE  '%john%'
OR c.ABN LIKE  '%john%'
OR c.Fax LIKE  '%john%'
OR c.CompanyName LIKE  '%john%'
OR o.purchaseNo LIKE  '%john%'
OR o.dateCreated LIKE  '%john%'
OR cr.courierName like '%john%'

我的猜测背后的原因:如果您删除 Couriers，则会出现结果集。因此，您在 cr.couriersID = o.couriersID 上没有找到匹配项，尽管匹配或不匹配，但您仍需要结果集，因此您需要 LEFT OUTER JOIN。

For more, see Pictorial representation on JOINs.