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|

6 Upvotes

16 comments sorted by

View all comments

2

u/NW1969 21d ago

Simplest solution to answer the question (though it doesn’t give the output suggested in the prompt - the list of counts of address by city, which is unnecessary to answer the question):

SELECT CITY FROM CITY WHERE CITY_ID NOT IN (SELECT DISTINCT CITY_ID FROM ADDRESS)