inner join?
I am making a database of attractions in TN, here are my tables
I want to find the number of landmarks and city names for any city.
And I want to list the name and attractions for this city. How can I do this?
I am trying to do it for the second one but it didn't work
SELECT attractions.attraction_Name, Cities.city_Name FROM Cities INNER JOIN attractions WHERE city_ID=1
Any suggestions?
This is what I get
source to share
As I mentioned in my comment: you miss the suggestion on
:
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 a cross product. Each record of the first table is concatenated with every record from the second table
Count request might look like this:
SELECT COUNT(*), Cities.city_Name
FROM Cities
INNER JOIN attractions ON attractions.city_ID = Cities.cities_ID
GROUP BY Cities.city_name
source to share
You need more for the counting part:
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 request to list attractions and city name:
SELECT attractions.attraction_Name, Cities.city_Name
FROM Cities
INNER JOIN attractions ON attractions.city_ID = Cities.cities_ID
You are missing ON
in your connection. Since this is MySQL this is valid , but now you are doing a cross join.
If you want to specify a specific city to select, add WHERE
after JOIN
and do something like WHERE Cities.cities_ID = n
, where n
is the id.
source to share
There are two queries in your question.
Number of attractions in the 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
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 trying to fix these problems. First, you had a missing ON clause in your JOIN operation. Without an ON JOIN clause, it generates all possible row pairs from two tables - a combinatorial explosion that could lead you to bananas.
Secondly, you will need a template COUNT(*) ... GROUP BY
for your resume.
(We've all been there.)
source to share
Assuming one or more relationships exist between the two tables, you are missing an on clause in your query, but once you join the two tables correctly, you can simply filter with the 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;
source to share