finding duplicates

[mxs]

I have mysql database with info about hotels and other accommodation.

When searching for a town/city that exists in more than one country, I wish to return list of available cities eg>

Boston, England

Boston, USA

 

field are just address fields ie. town and country

 

Just can get it to work

Anyone help ?

 

Michael

B&B Network

[fenway]

Won't a simple where clause work? What's not working?

[zeodragonzord]

So let's assume that you have a hotel that exists in Boston, England and one in Boston, USA.  You'll have two rows in the database table.

 

This gives you any city that appears more than once.

 

SELECT * FROM myTable GROUP BY city HAVING count(city) > 1;

 

This gives you the number of times each city appears more than once.

 

SELECT count(city), city FROM myTable GROUP BY city HAVING count(city) > 1;