0

I am trying to a search that returns results from a certain radius. I have the first step working but its when I try to expand the results by including the second table that it stops working. I don't think UNION is the way to go but Ieft that in to better explaing what I am trying to do:

SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN((48.4284207 -abs(dest.lat)) * pi()/180 / 2),2) + COS(48.4284207 * pi()/180 ) * COS( abs(dest.lat) * pi()/180) * POWER(SIN((-123.3656444 - dest.lng) * pi()/180 / 2), 2) )) 
      as distance 
FROM business dest having distance < 10500 and 
     (businessName LIKE '%web%') ORDER BY distance
UNION 
(
 SELECT b.* 
   FROM business b, keywords k 
  WHERE k.keyword 
   LIKE '%web%' and b.businessID=k.businessID
)

The second table had two columns a "keyword" and then a fk to the businessID from the first table.

Mahmut Ali ÖZKURAN
  • 1,120
  • 2
  • 23
  • 28
Mr. KD
  • 1
  • 1
  • I don't know if you are aware, but if you are using a MyISAM type table, MySQL has extra functions built in for calculating distance. http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points – benathon Mar 02 '13 at 20:47
  • I get syntax errors trying to use the JOIN SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN((48.4284207 -abs(dest.lat)) * pi()/180 / 2),2) + COS(48.4284207 * pi()/180 ) * COS( abs(dest.lat) * pi()/180) * POWER(SIN((-123.3656444 - dest.lng) * pi()/180 / 2), 2) )) as distance FROM business dest having distance < 10500 and (businessName LIKE '%web%' or bio LIKE '%web%') ORDER BY distance JOIN (SELECT b.* FROM business b, keywords k WHERE k.keyword LIKE '%web%' and b.businessID=k.businessID); – Mr. KD Mar 02 '13 at 20:52
  • Mahmut that gives me an error "#1221 - Incorrect usage of UNION and ORDER BY" – Mr. KD Mar 02 '13 at 20:59

1 Answers1

1

From the query in your comment, is seems like you want to find the businesses within some distanse that have 'web' in their businessName or in a related keyword in the linked keywords table.

If so, a query like this should do:

SELECT DISTINCT dest.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((48.4284207 -abs(dest.lat)) * pi()/180 / 2),2) + COS(48.4284207 * pi()/180 ) * COS( abs(dest.lat) * pi()/180) * POWER(SIN((-123.3656444 - dest.lng) * pi()/180 / 2), 2) )) 
      AS distance 
FROM business dest
LEFT JOIN keywords k
ON k.businessID = dest.businessID
WHERE dest.businessName LIKE '%web%' OR k.keyword LIKE '%web%'
HAVING distance < 10500 
ORDER BY distance
Terje D.
  • 6,250
  • 1
  • 22
  • 30
  • I'm getting this error now "#1054 - Unknown column 'distance' in 'where clause" I assume that means it's not making the "distance"? – Mr. KD Mar 02 '13 at 22:43
  • You cannot use "WHERE" with a computed value, you'll need to use "HAVING" as in the original query. – Niko Mar 02 '13 at 23:37