MySQL在多列上查询多个JOIN两个表

Question

I have a mysql query which interact from 2 tables, 'properties' and 'offers'.

The 'offers' table has can match a record in the property table by either referring to a specific record by a unique code or by the county or region the property is located.

Here's an example of my query...

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

In the offers table the are 3 columns the_property / the_county / the region are crucial for linking the appropriate offer with the property/ies. If an offer is to be applied to the entire county, the field the_property is blank, otherwise if an offer is for a specific property this field contains the unique property code.

I thought that by using multiple JOIN's would be the solution, however when the any of the 3 main offer fields are empty the join returns 'NULL' for the offers table fields.

How can this be resolved??

Many thanks

Answer 1

You can join the two tables and specify your extra join conditions in the join clause or the where clause

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