Confusing multiple JOIN question

Databases
1

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

Interesting database structure.

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


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

Thanks! That query kind of works, except if I try:

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

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

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

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

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…