SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code MySQL:
    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

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    finally got it to work and am now onto making it more efficient and quicker.

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •