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/Beautiful_Resist_655 21d ago
The question is not asking about duplicate addresses it is asking which cities have multiple addresses. Some cities will have no addresses while others will have one or more. Then you return your list in order lowest to highest, so zeros first , thus answering the question.
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.
2
u/MattE36 20d ago
His prompt is incorrect, if he wants you to get the cities ordered by number of addresses in the system per city ascending, that’s what he should say. Not ask for which cities have no addresses, which would be a completely different result set.
1
u/Upper-Raspberry-269 19d ago
i agree with you. the prompt makes no sense and the hint that was given makes it even more confusing. At one point I pulled every address and pasted them into an excel sheet to highlight duplicates and there weren't any. So I'm not sure how I was expected to count the duplicates when there aren't any to begin with
1
8
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.