Selecting a column with duplicate values in a table 1

[aj200100]

I have a table customer with the following fields
customerID,CustomerName,Address,City,state,zip. I am trying to write a query
to display all these fields only where the zipcode fields are the same.
My query looks somthing like this:

I have a table customer with the following fields
customerID,CustomerName,Address,City,state,zip. I am trying to write a query
to display all these fields only where the zipcode fields are the same. I am trying to select all rows from the table where all zipcodes occur more than once in the table. I am not looking for any specific zipcode.
My query looks somthing like this:

SELECT DISTINCT A.CUSTNMBR,A.CUSTNAME,A.ADRSCODE,A.CNTCPRSN,
(RTRIM(A.ADDRESS1) + ' ' + RTRIM(A.ADDRESS2) + ' ' + RTRIM(A.ADDRESS3)) AS ADDRESS,A.CITY,A.STATE,A.ZIP,COUNT(A.ZIP) AS ZIPCOUNT
FROM customer as a,customer AS B
where A.CUSTNMBR= B.CUSTNMBR AND A.ZIP=?????

The result from the query would look something like
Customer 1
CustomerID,CustomerName,.....,zip1
Customer2
CustomerID,CustomerName,....,zip1


Any help would be much appreciated.
Thank you!

 

[bborissov]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] A.CUSTNMBR,
                A.CUSTNAME,
                A.ADRSCODE,
                A.CNTCPRSN,
                ([COLOR=#FF00FF]RTRIM[/color](A.ADDRESS1) + [COLOR=red]' '[/color] +
                 [COLOR=#FF00FF]RTRIM[/color](A.ADDRESS2) + [COLOR=red]' '[/color] +
                 [COLOR=#FF00FF]RTRIM[/color](A.ADDRESS3)) [COLOR=blue]AS[/color] ADDRESS,
                 A.CITY,
                 A.STATE,
                 A.ZIP,
                 B.CountZIP [COLOR=blue]AS[/color] ZIPCOUNT
[COLOR=blue]FROM[/color] customer A
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Zip, [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]AS[/color] CountZIP
                   [COLOR=blue]FROM[/color] customer
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Zip
            [COLOR=blue]HAVING[/color] [COLOR=#FF00FF]COUNT[/color](*) > 1) [COLOR=blue]AS[/color] B
      [COLOR=blue]ON[/color] A.ZIP = B.Zip

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[aj200100]

Thank you very much, that worked!

 

[JasonFMC]

Try this:
SELECT Custnmbr, Custname, Adrscode, Cntcprsn,
(Address1 + ' ' + Address2 + ' ' +
Address3) AS Address, City, State,
Zip, COUNT(Zip) AS ZipCount
FROM Customer
WHERE Zip in (SELECT Zip FROM Customer GROUP BY Zip
HAVING COUNT(*) > 1)