CODEWITHSUNDEEP
mysql
Ashok
Ashok

Reputation: 25

use join in distance searching and mysql

In mysql I am calculating all the points between two zip codes and that's working fine for me. Now I want update that query to join an additional table, but I am getting an error. Here is the query please have a look:

SELECT *
FROM users
INNER JOIN
    (SELECT *,
            ACOS(
                  COS(RADIANS(30.7261629)) 
                * COS(RADIANS(lat_collection))
                * COS(RADIANS(76.7596221) - RADIANS(`long_collection`)) 
                + SIN(RADIANS(30.7261629)) 
                * SIN(RADIANS(lat_collection))
            ) * 3956 AS `distance`
    FROM quote q
    WHERE lat_collection BETWEEN 30.7261629 - (30 / 69) AND 30.3345816 + (30 / 69)
    AND `long_collection` BETWEEN 76.7596221 - (30 / (69 * COS(RADIANS(30.7261629)))) AND 78.0537813 + (30 / (69* COS(RADIANS(30.7261629))))
    ) AS u 
ON q.user_id=u.id
WHERE `distance` < 30

I need to join four-five table in the same query; how can I do that? I am getting an error when trying to join the tables (I have two zip codes and then I calculate lat long for both the zip codes).

Upvotes: 1

Views: 95

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You are using an alias in where condition .. this is not allowed you must reuse the full code instead of alias 

  SELECT * 
  FROM users  inner join (
    SELECT *, 3956 * ACOS(COS(RADIANS(30.7261629)) * COS(RADIANS(lat_collection)) * COS(RADIANS(76.7596221) -
    RADIANS(`long_collection`)) 
      + SIN(RADIANS(30.7261629)) * SIN(RADIANS(lat_collection))) AS `distance` 
    FROM quote q 
    WHERE lat_collection BETWEEN 30.7261629 - (30 / 69) AND 30.3345816 + (30 / 69) 
    AND `long_collection` BETWEEN 76.7596221 - (30 / (69 * COS(RADIANS(30.7261629)))) AND 78.0537813 + (30 / (69* COS(RADIANS(30.7261629))))
    ) as u on users.id=u.user_id 
    WHERE (3956 * ACOS(COS(RADIANS(30.7261629)) * COS(RADIANS(lat_collection)) * COS(RADIANS(76.7596221) - RADIANS(`long_collection`)) 
      + SIN(RADIANS(30.7261629)) * SIN(RADIANS(lat_collection)))) < 30

Upvotes: 1

Related Questions