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

What is the Problem with your query? Maybe the missing `on` clause? — Jens, Apr 28 '17 at 11:27
@Jens I'm not getting an error please take a look at the added image — Ahmed Al Abdulaal, Apr 28 '17 at 11:35
try `SELECT attractions.attraction_Name, Cities.city_Name FROM Cities INNER JOIN attractions on cities_id=City_id WHERE city_ID=1` — Jens, Apr 28 '17 at 11:37

Jens · Accepted Answer · 2017-04-28T11:44:13.440

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

score 1 · Answer 2 · edited May 23 '17 at 11:46

You want something more like this for the Counting portion:

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

And for your other query for listing the attractions and city name:

SELECT attractions.attraction_Name, Cities.city_Name 
FROM Cities 
INNER JOIN attractions ON attractions.city_ID = Cities.cities_ID

You're missing the ON in your join. Since this is MySQL, this is valid but now you're making a cross join.

If you want to specify a specific city to select, add a WHERE after the JOIN, and do something like WHERE Cities.cities_ID = n where n is the ID.

score 1 · Answer 3 · answered Apr 28 '17 at 11:44

Your question calls for two queries.

The count of attractions by city:

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

The catalog of all attractions in each city:

 SELECT Cities.city_Name, attraction.attraction_Name
  FROM Cities
  JOIN attractions ON Cities.cities_ID=attractions.city_ID
 ORDER BY Cities.city_Name, attraction.attraction_Name

You missed two things in your attempt to solve these problems. Firstly, you had a missing ON clause in your JOIN operation. Without an ON clause JOIN generates all possible pairs of rows from the two tables -- a combinatorial explosion that can drive you bananas.

Secondly, you need the COUNT(*) ... GROUP BY pattern for your summary.

(We've all been there.)

Thank you very much, the first one worked perfectly, but I'm getting all attraction names and cities names for the second one what if I just wanted one city? — Ahmed Al Abdulaal, Apr 28 '17 at 11:49
Add your `WHERE cities.cities_ID = 1` clause to limit your query to just one city. Put it before the `ORDER BY` clause. — O. Jones, Apr 28 '17 at 11:56

score 0 · Answer 4 · answered Apr 28 '17 at 13:18

assuming there's one to many relationships between the two tables, you are missing an on clause in your query, but once you join the two tables properly, you could simply filter with a where clause:

select c.city_name, a.attraction_name
 from cities c inner join attractions a on a.city_id = c.cities_id
 where city_id = 1;

inner join statement?

4 Answers4