inner join statement?

Question

I'm doing a database about attractions in TN, here are my tables

I want to find the number of attractions and the cities name for any given city

And I want to list the name and attractions for a given city How would I go about doing that?

I tries this for the second one but it did not work

SELECT attractions.attraction_Name, Cities.city_Name FROM Cities INNER JOIN attractions WHERE city_ID=1

Any suggestions?

This is what I get

Answer 1

As i mentioned in my comment: You miss the on clause:

SELECT attractions.attraction_Name, Cities.city_Name 
FROM Cities INNER JOIN attractions on cities_ID=city_ID WHERE city_ID=1

What you get is called cross product. Every entry of first table is joind with every entry from second table

The Count query can look like:

SELECT COUNT(*), Cities.city_Name 
FROM Cities 
INNER JOIN attractions ON attractions.city_ID = Cities.cities_ID
GROUP BY Cities.city_name