where city = 'Washinton' and state = 'NJ'
Im sorry I forgot to mention, there is no input for state. So I need to automatically know how to display each 'washington' from each state.
SELECT DISTINCT city, state.
Will return one and only one record for each unique combination of city and state in the specified table filtered by the specified where clause.
Most likely what you are looking for.
But your question does scream of a database design that may need some normalization. If you have multiple city and states in your database and you have need for this to be shown in such a manner, you database could possible benefit from a city & state table that normalizes this data.
City and state are stored as two fields in my mysql DB. I was considering using 'select distinct' but I wasnt sure how it would work in relation to two fields. So how would I go about making sure washington, nj only appears once (its in a database where there will be multiple washington, NJ listed).
DISTINCT works the same way for single field or multiple fields. It returns all distinct combinations of the values in the columns you have listed.
SELECT DISTINCT City, State
WHERE City = 'Washington'
(Data) City, State
So for your two columns it would distinct combinations of the "City" and "State" values. But why are there multiple "Washington, NJ" records? Are they actually different cities or duplicate records?
Thanks, I will test this out as soon as I am done with work. My database
consists entirely of zip codes, lat, long, city, state, etc. (its a list of
business locations) I want to make it so the user will generally search
city,state but in the event they just put city I want the db to be able to
handle that request as well. This is sort of a backup plan for people who
don't follow the search rules.
Does this make sense? Does this provide a heavier burden on the Db then
To clarify why there are multiple city state combos. Lets say I was mapping
out all of the McDonalds on the east coast. This would consist of a lot of
New York, NY entries in the Database because there are hundreds of mcdonals
in ny. Each row in the db consists of more information then just the city
and state, in this example it would hold infromation like phone number,
business name, email, etc etc
Is there any easier way to do this? Is my data being redudent?
Is there any easier way to do this?
Easier to maintain complex data, yes.
Is my data being redudent?
Yes it is redundant. Imagine a future where the Dutch finally return and reclaim their colony rechristened by the English as "New York", restoring it to its original name of "New Amsterdam". How many records for McDonald's in your database would you have to go and change?
A more normalized database design would be to have a "Cities" table that the main table would reference with a foreign key. Then all these McDonald's would just have the value of the index key for the "New York" record in their 'CityKey" field. Then one change in the city table to modify the "New York" record to "New Amsterdam" and you are covered if the Dutch every get imperial again.
With this type of data it can be balancing act between normalization and simplicity. And you really have to look at your applications needs and potential growth to decide if the increase in complexity warrants the benefits of simplicity in future maintenance.
I definitely see your point. I have been using this method purely out of
ignorance. I will look into switching over to using a foreign key to
reference and plan on switching over when I have time to implement such a
task. Right now my application is used on a very small scale. Ideally, I
would hope its use will grow so I am confident the switch will be
inevitable. I unfortunately learned how to get things to work, not
necessarily to work correctly or efficiently. But im starting to learn now!