sandirahomes
New Member
Guys urgent help needed!Well I ve a mysql database which has more than 158,000 records and there are some duplicate datas in it. I wish to list these duplicate datas To be more clear what exactly I'm looking for is:the mysql table name is postal. and the columns are id, street, zipcode, locality and city[*]id:1, zipcode: 123456, street: street1, locality: locality1, city: cityA[*]id:2, zipcode: 123456, street: street2, locality: locality1, city: cityA[*]id:3, zipcode: 123456, street: street3, locality: locality1, city: cityA[*]id:4, zipcode: 123457, street: street4, locality: locality1, city: cityA[*]id:5, zipcode: 123458, street: street5, locality: locality1, city: cityA[*]id:6, zipcode: 166666, street: street6, locality: locality26, city: cityDE[*]id:7, zipcode: 177777, street: street7, locality: locality38, city: cityEFThese values are allowed! This is a rough sketch of the datas that have been entered into mysql db table: postalSame zipcodes(eg: id=1,2,3) will have different street values but only one locality name is allowed for them. Also different zipcodes(eg: id=3,4,5) may or may not have different locality. Please don't get much confused about this.The problem I am facing is, I have found some record like these:[*]id:11, zipcode: 111111, street: street1, locality: locality1, city: cityA[*]id:25, zipcode: 111111, street: street2, locality: locality2, city: cityA[*]id:56, zipcode: 111111, street: street3, locality: locality3, city: cityAYou can notice that same zipcode(eg: id=11,25,56) is having entirely different locality values, which is not allowed. Only one locality value is permissible for same zipcode.Kindly suggest me the sql query string for filtering out zipcode containing different locality values. Please not there are more than 150,000 records.NB: I am not asking for sql query which can sort the values according to pincode, I am looking for a way to find zipcode having different locality values.