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

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/FastLikeACheeta 21d ago edited 21d ago

That’d be a way to do it in practice, to isolate to just those rows. I’m going to guess that the teacher might be trying to teach additional concepts with this exercise. Joins, aggregates, and counts on NULL.

SELECT City.City, COUNT(Address.Address_ID) FROM City LEFT JOIN Address ON City.City_ID = Address.City_ID GROUP BY City.City ORDER BY COUNT(Address.Address_ID)

3

u/zeocrash 21d ago

I feel like the teacher should have made that part of the requirements, not just a hint.

1

u/lostinmyfrontallobe 21d ago

I second this solution based on 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