CODEWITHSUNDEEP
mysql
KJS
KJS

Reputation: 1214

MYSQL add JOIN to query

We have this query for MYTABLE and would like to join OTHER_TABLE to get linked data from it. But it just doesn't seem to work.

SELECT *,3956 * 2 * ASIN(SQRT( POWER(SIN(($orig_lat - abs(wlatitude)) * pi()/180 / 2),2) 
+ COS($orig_lat * pi()/180 ) * COS(abs(wlatitude) *  pi()/180) * POWER(SIN(($orig_lon - wlongitude) *  pi()/180 / 2), 2) )) 
as distance FROM MYTABLE dest having distance < $dist ORDER BY distance

How can we add this to the query without getting errors?

LEFT JOIN OTHER_TABLE ON MYTABLE.column=OTHER_TABLE.column

Upvotes: 0

Views: 73

Answers (4)

AdrianBR
AdrianBR

Reputation: 2588

Whatever you will do, you will not get 'errors' but rather perhaps results you do not need.

If the join is 1 to 1, or many to one, you will not get any multiplied rows.

If the join is one to many, you may get multiplied content on the left side for each number of corresponding rows on the right side, depending on your condition.

see http://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators for the join logic.

to your query, and for any other query, the techincal order is :

select # ... 
from # table 
join # table
on  # condition
join # another table
on # another condition
where # condition
group # clause
having # condition applied after grouping
order by # ..

.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Using left join there may be a possibility to get duplicates try this with using distinct also use proper aliases for tables 

SELECT DISTINCT 
  *,
  3956 * 2 * ASIN(
    SQRT(
      POWER(
        SIN(
          ($orig_lat - ABS(dest.wlatitude)) * PI() / 180 / 2
        ),
        2
      ) + COS($orig_lat * PI() / 180) * COS(ABS(dest.wlatitude) * PI() / 180) * POWER(
        SIN(($orig_lon - dest.wlongitude) * PI() / 180 / 2),
        2
      )
    )
  ) AS distance 
FROM
  MYTABLE dest 
  LEFT JOIN OTHER_TABLE o ON dest.column=o.column
HAVING distance < $dist 
ORDER BY distance

Upvotes: 0

arilia
arilia

Reputation: 9398

SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN(($orig_lat - abs(wlatitude)) * pi()/180 / 2),2) 
+ COS($orig_lat * pi()/180 ) * COS(abs(wlatitude) *  pi()/180) * POWER(SIN(($orig_lon - wlongitude) *  pi()/180 / 2), 2) )) AS distance 
FROM MYTABLE dest 
LEFT JOIN OTHER_TABLE ON dest.column = OTHER_TABLE.column 
HAVING distance < $dist ORDER BY distance

Upvotes: 0

Orel Eraki
Orel Eraki

Reputation: 12196

Haven't gone deep into your query but it should be like this.

SELECT
    *,
    3956 * 2 * ASIN(SQRT( POWER(SIN(($orig_lat - abs(wlatitude)) * pi()/180 / 2),2) + COS($orig_lat * pi()/180 ) * COS(abs(wlatitude) *  pi()/180) * POWER(SIN(($orig_lon - wlongitude) *  pi()/180 / 2), 2) ))
        as distance
FROM MYTABLE dest
LEFT JOIN OTHER_TABLE ON MYTABLE.column=OTHER_TABLE.column
HAVING distance < $dist
ORDER BY distance

Upvotes: 0

Related Questions