Query acting strangely ;)

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

finally got it to work and am now onto making it more efficient and quicker.

bazz