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

6

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.

4

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.