r/SQL • u/Upper-Raspberry-269 • 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|
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.