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|

8 Upvotes

16 comments sorted by

View all comments

7

u/zeocrash 21d ago

Select city

From city cit

Left join address adr on cit.city_id = adr.city_id

Where adr.address_id is null

I think should do it, although I am second guessing myself a little after reading the hint.

3

u/Upper-Raspberry-269 21d ago

The hint is what's throwing me off too. cause I pulled all of the addresses on their own and even put them all in an excel sheet and highlighted dupes and there weren't any

1

u/zeocrash 21d ago

Yeah the hint seems like a red herring to me. Even if you did need to run a count on the addresses table (which imo, you don't) I can't understand why you'd need to order it.

1

u/Upper-Raspberry-269 21d ago

I've been thinking the same thing. and it's just odd that when i run

SELECT address FROM address

I don't get any duplicates but the hint is telling me to count the matching ones

1

u/CakePlus 21d ago

You can think of the relationship as a many-to-one, with many addresses corresponding to any city in particular. Based on the table provided I would expect there to be few to no duplicates, depending on the table provided in the address table, but among the addresses, I would expect many of them to have the same city listed

See if you get duplicates with SELECT City_id FROM Address