duplicates recrods from database search

[tacomafred]

Here is my resulting page from a selet statement

http://www.resistolhat.com/raw/retailers/locator_results.asp?select=OK&Submit=search

There are a few cities that come up more than twice, I need to only show each city once? Is there are a statement for this?

Thanks,
Fred

[Newsgroup_User]

Have you used SELECT DISTINCT?

Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
Dell stuff: http://robgt.com/dell
SatNav stuff: http://robgt.com/satnav

[tacomafred]

no, I'm not sure how to use it without writing an actual select statement... I want to build it into the actual link? Is that possible?

[jeremyluby]

use the word distinct in your select statement

such as:

select distinct city_name from table where blah blah blah

[tacomafred]

The previsous page is here

http://www.resistolhat.com/raw/locator.asp

Can i do this while avoiding an actual select statement, can I just hard code it into the actual link?

I want the user to select the state and the results to display all of the cities from the selected state

Thank you for your time

Fred

[jeremyluby]

Well since your database shows the entries more than once you cannot avoid using the distinct.

Have you considered normalizing your tables?

[tacomafred]

So i guess I will need to build a select statement? I'm just not sure how to do this.

Do I bult it on the map page or the results page? I have each state linked with a image map, so I will have to build that select satement for each state?

[jeremyluby]

you could pass the state in the url to the results page

then make a dynamic select statement on the results page like so:

select distinct city_name from table_name where state = $_GET['city_variable_from_URL'];

[tacomafred]

Here is the orginal select statement from the resutls page....

locator_rs.Source = "SELECT * FROM Retailers WHERE State = '" + Replace(locator_rs__MMColParam, "'", "''") + "'"

Here is what I think you told me to do....



locator_rs.Source = "SELECT DISTINCT * FROM Retailers WHERE State = '$_GET['city_variable_from_URL', "'", "''") + "'"

[jeremyluby]

well I have no idea what you are going to call your variable in the URL.

Assuming that you have an auto-incremented field in your table a select distinct * will return all the items, because no two rows can be the same.

you will need to use "select distinct field1, field2, field3 from . . . "

[tacomafred]

ok, I think I got it...

locator_rs.Source = "SELECT DISTINCT STATE FROM Retailers WHERE State = '$_GET['city_variable_from_URL', "'", "''") + "'"


Is everything else ok?

[jeremyluby]

more like select distinct cities from . . . .

[tacomafred]

ok, I will keep trying. Thank you for your time.