Hi,
Been struggling with this for about 24hrs. I am querying two tables (businesses & address) to output some business data and the distances between the base business and others around it.
I can manage to output the ‘correct’ distances but, in that case, the same business details are shown for each distance.
Alternatively, I can output the correct business data but the distance is wrong.
I reckon it is related to my joins but I have all join types I can think of. I have put each table in different orders such that now, the query will return firstly; just those businesses within the required distance and only then, wil it try to retrieve the business data. Unfortunately, this still outputs one business data for all distances.
Would anyone be able to tell me what I am overlooking? The individual parts of the query work in thier own right so ~ rightly or wrongly ~ I am convinced it is a join issue of some sort.
SELECT SQL_CALC_FOUND_ROWS
bd.business_id
, bd.business
, bd.business_sub_type
, bd.business_category
, bd.web_url
, f.file_text
, dist_mi
from
(SELECT
((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
, business_id
FROM
(SELECT
SQRT(dx * dx + dy * dy + dz * dz) AS d
, business_id
FROM
(SELECT
p1.business_id
, (6378 * COS(RADIANS(p1.Latitude)) * COS(RADIANS(p1.Longitude))) - (6378 * COS(RADIANS(p2.Latitude)) * COS(RADIANS(p2.Longitude))) AS dx
, (6378 * COS(RADIANS(p1.Latitude)) * SIN(RADIANS(p1.Longitude))) - (6378 * COS(RADIANS(p2.Latitude)) * SIN(RADIANS(p2.Longitude))) AS dy
, (6378 * SIN(RADIANS(p1.Latitude))) - (6378 * SIN(RADIANS(p2.Latitude))) AS dz
FROM address AS p1
JOIN address AS p2
ON p1.business_id != p2.business_id
) t1
) t2
) qq1
join business_details as bd
on qq1.business_id = bd.business_id
join files AS f
on f.business_id = bd.business_id
and f.file_name = 'search summary'
where dist_mi <=10
#and bd.business_sub_type = ?
ORDER
BY dist_mi
#limit $search_start, $entries_per_page
pulling my hair out strand by strand and already am making more progress than with this query.
bazz