Selecting a column with duplicate values in a tabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting a column with duplicate values in a tabl

Author

Topic

aj200100
Starting Member

5 Posts

Posted - 2009-07-27 : 17:55:25

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.
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!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-27 : 18:34:45

Do you just mean where the zip is specific? If so, you're almost there. This will give you all the info where the customer has that zip


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
INNER JOIN customer AS B ON
A.CUSTNMBR = B.CUSTNMBR
WHERE   A.ZIP = <specificzip>
GROUP BY
A.CUSTNMBR,A.CUSTNAME,A.ADRSCODE,A.CNTCPRSN,
(RTRIM(A.ADDRESS1) + ' ' + RTRIM(A.ADDRESS2) + ' ' + RTRIM(A.ADDRESS3)),A.CITY,A.STATE,A.ZIP

aj200100
Starting Member

5 Posts

Posted - 2009-07-28 : 10:41:49

Thank you for your response, but no I am not searching for a specific zipcode. I am selecting all rows from the table where all zipcodea occur more than once in the table.

aj200100
Starting Member

5 Posts

Posted - 2009-07-28 : 10:44:19

Thank you for your response but I am not actually searching for a specific zip code. I am trying to select all rows from the table where a zipcode value occurs more than once in the table.

aj200100
Starting Member

5 Posts

Posted - 2009-07-28 : 10:46:09

Thank you for your response but I am not actually searching for a specific zip code. I am trying to select all rows from the table where a zipcode value occurs more than once in the table.

aj200100
Starting Member

5 Posts

Posted - 2009-07-28 : 16:16:47

I have the solution, here it is:
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--,
-- B.CountZIP AS ZIPCOUNT
FROM customer A
INNER JOIN (SELECT Zip, COUNT(*) AS CountZIP
FROM customer
GROUP BY Zip
HAVING COUNT(*) > 1) AS B
ON A.ZIP = B.Zip
order by zip,CUSTNMBR,CUSTNAME asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources