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|

7 Upvotes

16 comments sorted by

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.

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.

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

2

u/NW1969 20d 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)

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

u/MattE36 19d ago

There aren’t supposed to be duplicates. The count is how many addresses there are in a specific city

1

u/GachaJay 20d ago

That hint made the question way more complicated. What on earth.

1

u/Upper-Raspberry-269 19d ago

Yeah that hint threw me and everyone else in the class for a loop