CODEWITHSUNDEEP
phpmysqlsqldatabaseselect
David G
David G

Reputation: 301

MySQL query for multi JOIN two tables on multiple colums

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

Upvotes: 0

Views: 217

Answers (4)

Barmar
Barmar

Reputation: 781290

If you want the most specific offer to take precedence, I think you have to write it like this:

SELECT prop.*,
       COALESCE(offers.col1, offsCnty.col1, offsRgn.col1) col1,
       COALESCE(offers.col2, offsCnty.col2, offsRgn.col2) col2,
       ...,
       <huge formula> 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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You have three different offers tables all with the same field names. The problem is that MySQL does not allow multiple columns with the same name.

The easiest fix is to change the join to use or in the on 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` prop LEFT JOIN
     `offers`
      ON prop.code = offers.the_property or
         (prop.county = offsCnty.the_county and offers.the_property is null) or
         prop.region = offsRgn.the_region
HAVING distance <= 2.5
ORDER BY `sleeps` ASC, `distance` ASC
LIMIT 0, 10;

If you do use multiple joins, then you should have expressions such as the following in the select clause:

select coalesce(offers.code, offsCnty.code, offsRgn.code) as code

for each column in the offers table.

Upvotes: 0

Marek
Marek

Reputation: 7433

Columns from later offers tables overwrite earlier ones, you need to alias them:

SELECT *, offers.the_property the_property_from_offers, ...

Upvotes: 0

DevZer0
DevZer0

Reputation: 13535

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

Upvotes: 1

Related Questions