php - MySQL 查询多个列上的多个 JOIN 两个表

Question

我有一个 mysql 查询，它与 2 个表“properties”和“offers”进行交互。

“优惠”表可以通过通过唯一代码或按特性所在的县或地区引用特定记录来匹配特性表中的记录。

这是我的查询示例...

SELECT *, ROUND(((3959 * acos(cos(radians(51.1080390)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-4.1610140)) + sin(radians(51.1080390)) * sin( radians(latitude)))) * 2),0)/2 AS `distance`
FROM `properties` AS prop
LEFT JOIN `offers` ON prop.code = offers.the_property
LEFT JOIN `offers` AS offsCnty ON prop.county = offsCnty.the_county
LEFT JOIN `offers` AS offsRgn ON prop.region = offsRgn.the_region
HAVING distance <= 2.5
ORDER BY `sleeps` ASC, `distance` ASC
LIMIT 0, 10

在优惠表中，有 3 列 the_property/the_county/theregion 对于将适当的优惠与特性链接起来至关重要。如果要约适用于整个县，则 the_property 字段为空，否则，如果要约针对特定特性，则该字段包含唯一的特性代码。

我认为使用多个 JOIN 将是解决方案，但是当 3 个主要 offer 字段中的任何一个为空时，连接将为 offer 返回“NULL”表字段。

如何解决这个问题？

非常感谢

Answer 1

您可以连接两个表并在 join 子句或 where 子句中指定额外的连接条件

SELECT *, ROUND(((3959 * acos(cos(radians(51.1080390)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-4.1610140)) + sin(radians(51.1080390)) * sin( radians(latitude)))) * 2),0)/2 AS `distance`
FROM `properties` AS prop
LEFT JOIN `offers` ON prop.code = offers.the_property 
       OR prop.county = offers.the_county 
       OR prop.region = offers.the_region
HAVING distance <= 2.5
ORDER BY `sleeps` ASC, `distance` ASC
LIMIT 0, 10