0

I have a table of cities, states based on zip codes. So if someone searches chicago they'll get like 30 listings of chicago, il because of the number of zip codes. I'd like to only get one listing of chicago... but I'd also like to return the zip field too (because I don't need it exact). Below will return a single row of "chicago, il" but it won't also pass the zip... how can it also pass the zip.

SELECT DISTINCT City, State FROM Zips where City like 'chicago' ORDER BY State

Zips table

*Zip | City | State | Lat | Long
Improve this question
4
  • How do you know which ZIP code you want if you're searching by city name? Commented Apr 5, 2011 at 4:39
  • Can you give me the table definition for ZIPS ? I think there's a flaw in your current description, meaning you haven't selected zips at all o.o Commented Apr 5, 2011 at 4:40
  • because the zip is in the same row as the city and state Commented Apr 5, 2011 at 4:41
  • Right, but there's no criteria you're giving other than the city name. You're asking for a ZIP code and you're getting back more than one ZIP code in more than one row; how do you know which one is "correct"? Commented Apr 5, 2011 at 4:49

4 Answers 4

Reset to default
3 
SELECT City, State, MIN(Zip) AS ZIP
  FROM Zips
 WHERE City = 'chicago'
 GROUP BY City, State
 ORDER BY State;

Or, maybe more informatively:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
 GROUP BY City, State
 ORDER BY State;

Would it also be possible to allow the input search to take a zip code and/or a city?

Search on zip code:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

Search on zip and city:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
   AND Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

Search on zip or city:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
    OR Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

It is trickier to use a single query to search on zip (unless it is null) or city (unless that is null). It typically ends up using question mark placeholders etc - and is not wholly portable across DBMS (whereas the queries above are all portable to any SQL DBMS).

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE (? IS NOT NULL AND City = ?)
    OR (? IS NOT NULL AND Zip  = ?)
 GROUP BY City, State
 ORDER BY State;

Here, if it works, you'd provide the city parameter twice (once for each of the first two question marks) and the zip parameter twice (once for each of the last two question marks). This gets into the realm of discussing which programming language too. Some DBMS would allow you to write each parameter once, using a notation such as ':n' to identify the different parameters:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE (:1 IS NOT NULL AND City = :1)
    OR (:2 IS NOT NULL AND Zip  = :2)
 GROUP BY City, State
 ORDER BY State;

I don't know what MySQL provides in this area.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

would it also be possible to allow the input search to take a zip code and/or a city?
These examples are great. It's now working with the zip or the city. I'm using jquery to auto-populate on keystrokes... the only issue i now see is that unless the user types all 5 zip codes the wrong city,state might auto-populate. (i'd like it to be 100% accurate but the distances aren't across town... they are like 50-90 miles so a couple of more miles isn't too big of a deal... it is just there to give folks an idea of where they are located.
one more question. is it possible to allow the input field to also allow the city, state (chicago, il) to be part of the search as well as the just the city?
BTW - the State field is an abbreviation, like IL for Illinois
@Jeffrey: yes - of course. Simply adapt any of the queries that uses City to specify State instead of, or as well as, City. In terms of input, that will depend on the programming language(s) that you're using. There are various ways that the information can be entered.
1

Assuming the Zip code is in a field called Zip, you could try:

SELECT City, State, MIN(Zip) 
FROM Zips 
WHERE City LIKE 'chicago'
GROUP BY City, State

For what you're describing, you're going to be forced to select either the MIN or MAX zip code.

Improve this answer

Comments

0

If I understand that you don't care which zip code you get back I think you're looking for

LIMIT 1

Put it at the end of your SQL statement. 

SELECT City, State, Zipcode FROM Zips where City like 'chicago' LIMIT 1;

Unless you've possibly got cities that occur in multiple states?

Improve this answer

2 Comments

That works as long as you don't need the information from Chicago in any other state (Michigan, Tennessee, Illinois all have a city called Chicago, and you don't know which one you'll get).
Right ... ergo my trailing question. Though in hindsight I think just getting a min() or max() zip is probably the way to go provided they're stored as a numeric value and not a string
0

Assuming that Zips is unique on Zip, another solution would be:

Select Zip, City, State, Lat, Long
From Zips
Where Zip   =  (
                Select Min(Z1.Zip)
                From Zips As Z1
                Where Z1.City = 'chicago'
                )

That arbitrarily chooses the "lowest" zip code for any city named "chicago". However, I would be surprised that if that would suffice. A search for a zip code purely based on city name is never going to be logically sufficient. At the very least, you would want to narrow it by State or show all States which contain the requested city.

If you wanted to search for the zip code or city, you could do:

Select Zip, City, State, Lat, Long
From Zips
Where Zip   =   (
                    Select Min(Z1.Zip)
                    From Zips As Z1
                    Where Z1.City = 'chicago'
                        Or Z1.Zip = '12345'
                    )
Improve this answer

2 Comments

okay, how the page works is... it auto-detects the users current lat/long based on ip and fills in a city, state in the location input box. Though the user may not want to search within their current location hence why i wanted it to search by city. As the user keystrokes jquery ui autofills the city,state data. I realize this is never going to be entirely accurate but it's not a map-based website so a couple of miles off calculation is fine. Although I would also like the input location box to be able to also accept zip codes. How could I set up a dual functioning input field?
@Jeffrey - Assuming we are only talking about US addresses, you could simply determine if the search term is numeric and if so, assume it is a zip code. In fact, there are only a limited number of forms you need to handle: zip only, city (+long/lat), city [, or space] state (and maybe long/lat), city [, ] state zip, city zip. Those should narrow your results to a row or two on any query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.