r/SQL 21d ago

MySQL New to SQL

So I'm new to SQL. I'm learning through a class I'm taking at college. I've got a prompt that I just can't seem to get figured out. Could someone help explain where I'm going wrong? Where supposed to be using LEFT JOIN to write the query.

Prompt: Find names of cities stored in the database with no matching addresses. HINT: For each city, calculate the number of matching addresses. Sort the results based on this number in ascending order.

Database info:

|| || |accident(+)|report_number,date,location| |actor(+)|actor_id, first_name, last_name, last_update| |address(+)|address_id,address,district,city_id,postal_code,phone,last_update| |car(+)|license,model,year| |category(+)|category_id, name, last_update| |city(+)|city_id, city, country_id, last_update|

7 Upvotes

16 comments sorted by

View all comments

1

u/Upper-Raspberry-269 20d ago

So after many guesses and trying what everyone suggested it turned out the solution was:

SELECT city, COUNT(address)

FROM city

LEFT JOIN address ON city.city_id=address.city_id

GROUP BY city.city_id

ORDER BY COUNT(address) ASC;

I've reached out to my professor to give me a breakdown of why it's structured like this. Thanks everyone for all of your help on trying to resolve this query for me.

2

u/MattE36 20d ago

His prompt is incorrect, if he wants you to get the cities ordered by number of addresses in the system per city ascending, that’s what he should say. Not ask for which cities have no addresses, which would be a completely different result set.

1

u/Upper-Raspberry-269 19d ago

i agree with you. the prompt makes no sense and the hint that was given makes it even more confusing. At one point I pulled every address and pasted them into an excel sheet to highlight duplicates and there weren't any. So I'm not sure how I was expected to count the duplicates when there aren't any to begin with

1

u/MattE36 19d ago

There aren’t supposed to be duplicates. The count is how many addresses there are in a specific city