MySQL select from multiple tables with LEFT JOIN

Question

Im having sql issues with the below query, its returning the error...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''

I cant see why this is throwing an error. When i remove parts of the join it runs correctly.

The jobs of this query is to return a list of properties based on the location given (lat/long) with any matching data from separate offers table along with the url slug for the town and county which are stored in separate tables towns and counties accordingly. If a property has no special offer, it should still be return in the data.

The query is...

    SELECT COUNT(agencyid) AS `count_offers`, prop.*, offers.*, twn.slug AS `slug_town`, cnty.slug AS `slug_county`, ROUND(((3959 * acos(cos(radians(50.1854670)) * cos(radians(prop.latitude)) * cos(radians(prop.longitude) - radians(-5.4209100)) + sin(radians(50.1854670)) * sin( radians(prop.latitude)))) * 2),0)/2 AS `distance` 
FROM `properties` AS `prop`

JOIN `towns` AS `twn` ON twn.name=prop.town
JOIN `counties` AS `cnty` ON cnty.name=twn.county
LEFT JOIN `offers` ON offers.agencyid = prop.mID
AND 
offers.dt_expire>1377985886 
AND 
(prop.sleeps >= offers.sleeps_min AND prop.sleeps <= offers.sleeps_max)

AND 
(
  prop.slug_code = offers.the_property 
  OR 
  (
    (offers.the_property='' OR offers.the_property=NULL) 
    AND 
    (
      (
        prop.county = offers.the_county 
        AND
        prop.town=offers.the_town
      ) 
      OR 
      (
        prop.county = offers.the_county
        AND
        twn.name=offers.the_town
        AND
        prop.place = offers.the_place
      ) 
      OR
      prop.county = offers.the_county 
      OR
      prop.region = offers.the_region 
      OR
      prop.country = offers.the_country
    )
  )

Thanks for you help regarding this.

Does it help if you change (offers.the_property='' OR offers.the_property=NULL) to ((offers.the_property='') OR (offers.the_property IS NULL)) ? — Hugh Jones
– Hugh Jones, Commented Sep 2, 2013 at 11:54

Romesh · Accepted Answer · 2013-09-02 12:16:27Z

1

In MySQL, if you need to comapre with NULL then syntax is Colunname IS NULL

SELECT COUNT(agencyid) AS `count_offers`, prop.*, offers.*, twn.slug AS `slug_town`, cnty.slug AS `slug_county`, ROUND(((3959 * acos(cos(radians(50.1854670)) * cos(radians(prop.latitude)) * cos(radians(prop.longitude) - radians(-5.4209100)) + sin(radians(50.1854670)) * sin( radians(prop.latitude)))) * 2),0)/2 AS `distance` 
FROM `properties` AS `prop`

JOIN `towns` AS `twn` ON twn.name=prop.town
JOIN `counties` AS `cnty` ON cnty.name=twn.county
LEFT JOIN `offers` ON offers.agencyid = prop.mID
AND 
offers.dt_expire>1377985886 
AND 
(prop.sleeps >= offers.sleeps_min AND prop.sleeps <= offers.sleeps_max)

AND 
(
  prop.slug_code = offers.the_property 
  OR 
  (
    (offers.the_property='' OR offers.the_property IS NULL) 
    AND 
    (
      (
        prop.county = offers.the_county 
        AND
        prop.town=offers.the_town
      ) 
      OR 
      (
        prop.county = offers.the_county
        AND
        twn.name=offers.the_town
        AND
        prop.place = offers.the_place
      ) 
      OR
      prop.county = offers.the_county 
      OR
      prop.region = offers.the_region 
      OR
      prop.country = offers.the_country
    )
  )
)

edited Sep 2, 2013 at 12:16

answered Sep 2, 2013 at 11:56

Romesh

2,3023 gold badges25 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RandomSeed Over a year ago

Yes, comparing anything with NULL always returns NULL.

Romesh Over a year ago

I added one ) at the end and instead of = NULL I replaced IS NULL.

RandomSeed · Accepted Answer · 2013-09-02 11:58:03Z

0

One closing parenthesis is missing (possibly in the last line).

answered Sep 2, 2013 at 11:58

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Comments

Hugh Jones · Accepted Answer · 2013-09-02 13:54:40Z

It is very tricky to answer a question like this without some sample data or table structures to play with, but I suggest this is what you are after.

If I remember correctly MySql does not evaluate '' to NULL, but if I am wrong then take the condition from my comment above.

SELECT 
  COUNT(agencyid) AS `count_offers`, 
  prop.*, 
  offers.*, 
  twn.slug AS `slug_town`, 
  cnty.slug AS `slug_county`, 
  ROUND(((3959 * acos(cos(radians(50.1854670)) * cos(radians(prop.latitude)) * cos(radians(prop.longitude) - radians(-5.4209100)) + sin(radians(50.1854670)) * sin( radians(prop.latitude)))) * 2),0)/2 AS `distance` 
FROM 
  `properties` AS `prop`
  JOIN `towns` AS `twn` ON 
    twn.name=prop.town
  JOIN `counties` AS `cnty` ON 
    cnty.name=twn.county
  LEFT JOIN `offers` ON 
    offers.agencyid = prop.mID AND 
    offers.dt_expire>1377985886 AND 
    prop.sleeps >= offers.sleeps_min AND 
    prop.sleeps <= offers.sleeps_max AND
    (
      prop.slug_code = offers.the_property OR 
      (
        (IfNull(offers.the_property, '') = '') AND 
        (
          (
            prop.county = offers.the_county AND
            prop.town=offers.the_town
          ) 
          OR 
          (
            prop.county = offers.the_county AND
            twn.name=offers.the_town AND
            prop.place = offers.the_place
          ) 
          OR
          prop.county = offers.the_county OR
          prop.region = offers.the_region OR
          prop.country = offers.the_country
        )
      )
    )

Collectives™ on Stack Overflow

MySQL select from multiple tables with LEFT JOIN

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related