SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2005
    Location
    Maine
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Confusing multiple JOIN question

    Hey guys,

    I have a JOIN problem that is really confusing me. I would appreciate any help.

    I have a database of golf courses and I want to find all the golf courses less than X distance from a certain city in a certain state. This is how my tables are set up:

    states: id, abbreviation
    cities: id, city, state_id
    courses: id, etc...
    distances_between: course_id, city_id, distance

    Basically "distances_between" is my junction table and tells me how far courses are from various cities. I want to be able to find any nearby courses, given a city name (cities.city) and a state abbreviation (state.abbreviation).

    Thanks guys....


  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting database structure.

    Assuming you are starting with a city_id and a distance, you could do something like:

    Code:
    SELECT c.*
    FROM courses AS c
        INNER JOIN distances_between AS db ON db.course_id = c.id
    WHERE db.city_id = 123 -- Replace with your City ID
        AND db.distance < 123; -- Replace with your Max Radius
    Now, I'm wondering why your database is structured this way. It would be infinitely more useful if you had an address or latitude / longitude stored for each course. That way, you could give exact distances not only from major cities, but from any specific location (zip code, address, cell phone w/ GPS, etc), as well as plot the course on a map and list driving directions to get there.

    Just some thoughts... obviously you know the solution you have to build, and can structure it however you need to. I always like to think of possible future additions of features when architecting something though.

    Cheers.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2005
    Location
    Maine
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! That query kind of works, except if I try:

    Code:
    SELECT c.*
    FROM courses AS c
        INNER JOIN distances_between AS db ON db.course_id = c.id
    WHERE db.city_id = 4
        AND db.distance < 10
    It also returns cities with totally different IDs, like 1503. Any idea why that is?

    The reason my data is set up this way is because as of now it is just a scrape of another website. I may add in the latitude/longitude functionality though, thank you for the idea.


  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shouldn't be returning cities at all... only courses. Are you sure it's not course ID #1503, which would be less than 10 (miles?) from city #4?

  5. #5
    SitePoint Addict
    Join Date
    Feb 2005
    Location
    Maine
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, it's the "city_id" column of courses. I have that because each course has a "home" city although it might be within a distance of nearby cities. Do you think that could be confusing the query somehow? When I run it through phpMyAdmin (hsssss) it highlights the city_id field.

    Thanks


  6. #6
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    reduce the distance to 1 and see if that changes things. There are many cities within 10 miles of each other... absolutely makes sense that you'll get courses in multiple cities in results like that.

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I do agree with transio's recommendation of a different database structure for distance - what you've described is an exponential growth situation that will inevitably lead to issues.

    As far as extraneous results, try adding db.distance to your SELECT clause; that should give you an idea of why the result was returned...


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
  •