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 2000 Forums
 Transact-SQL (2000)
 SQL statement help needed!

Author  Topic 

robertk53
Starting Member

9 Posts
Posted - 2004-11-15 : 17:52:39
Hi all,

I have a simple table which has 2 columns which is used to store zip codes.

bsID (Primary key)
pcMyZips (column which stores the zip codes)


My members can enter up to 5 distinct zip codes into this table, but sometimes they make the mistake of entering in duplicates.

Which SQL statement would I need to find out which members entered in duplicate zip codes. The statement also needs to list all zip codes the member entered, not just the duplicates.

So for instance the results would return:

BsID 2300
pcMyZips 90298, 90256, 90245, 90210, 90210

Thanks in advance for any help!

Robert

nr
SQLTeam MVY

12543 Posts
Posted - 2004-11-15 : 20:02:20
pcMyZips - multiple zip codes in one column? Bad design.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-11-15 : 20:24:14
Why exactly 5 zipcodes to a bsId ?
You really should redesign the table(s).

SQL server is a database not a texteditor,
not even in Excel would you store the information this way!

rockmoose
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-15 : 21:43:15
quote:
Originally posted by rockmoose

Why exactly 5 zipcodes to a bsId ?
You really should redesign the table(s).

SQL server is a database not a texteditor,
not even in Excel would you store the information this way!

rockmoose


My members indicate where they provide their services, which could be in up to 5 zip code area's. It may be bad design but so far I have had no problems with it.

Hopefully you will be able to help me with the SQL statement

Cheers,
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-16 : 06:32:55
quote:
It may be bad design but so far I have had no problems with it.
You're getting duplicates that you don't want, I'd say that's a problem with the design.
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 08:18:02
I should make it clear that I have limited knowledge with sql server and the language. I've received 3 responses which all tell me the design is wrong! OK UNDERSTOOOD!!!!!!

I plan on added a client side script which will disallow duplicates in the near future. But in the meantime, if some one could help me with the statement I need to find the duplicates I would appreciate it greatly.

Thank you
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2004-11-16 : 08:29:10
quote:
I've received 3 responses which all tell me the design is wrong! OK UNDERSTOOOD!!!!!! I plan on added a client side script which will disallow duplicates in the near future.
Sorry, but you DON'T understand. The reason you have duplicates is because of your table design, period. You can add all the client side code you want, you still won't have a table that will prevent duplicate entries.

No one is trying to bust your balls here, we are pointing out that your current design will not serve your needs, and unless you change it you'll continue to have this problem.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2004-11-16 : 08:40:47
[code]create table #x(bsid int, zips varchar(100))
insert #x select 1, '90298, 90256, 90245, 90210, 90210'
insert #x select 2, '90298, 90256, 90245, 90210'
insert #x select 3, '90298, 90256, 90245'
insert #x select 4, '12569, 12569'
insert #x select 5, '90298'
insert #x select 5, null


select bsid, min(zips) as has_duplicates from
(
select
bsid,
zips,
substring(replace(replace(zips,',',''),' ',''),ix,5) as zip
from
#x
cross join
( select 1 ix union select 6 union select 11 union select 16 union select 21 ) ix
where
len(substring(replace(replace(zips,',',''),' ',''),ix,5)) = 5
) duppers
group by
bsid
having
count(*) > count(distinct zip)

drop table #x

bsid has_duplicates
----------- ---------------------------------
1 90298, 90256, 90245, 90210, 90210
4 12569, 12569
[/code]

edit.
In a good mood today,
btw, it is easier to get data out of a normalized database
rockmoose
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 11:35:45
Hi Rockmoose,

Thanks for the reply!

I was hoping to stay away from creating additional tables. Is it not possible to use a simpler SQL statement to create the list of duplicate zip codes, or is this the only way to do it?.

Robert
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-11-16 : 12:19:24
quote:
Originally posted by robvolk

[quote]No one is trying to bust your balls here


Hmmmm....

ANYWAY...

The moose is just creating a table so you can cut and paste the example in to queary analyzer so you can see it run.

The SQL statement he wrote (quite cleverly) shows you have to find the dups on a single row of data...which I think (only sometimes mid you) is what you're looking for.

Now on to Rob's point.

Your table should look like:


CREATE TABLE myTable99(
	  bsid int
	, zipcode char(5)
	, PRIMARY KEY (bsid, zipcode))



And you wouldn't have this problem.

This is just 1 illustration as to why it is extremely important to have a sound database design BEFORE application development begins...

If not, it's usually to late, and then you have a big mess...

I know entire "consulting" firms who have made a living out of doing this...

Arthur A., TD W. Price C....worked with them all....
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-11-16 : 12:23:20
Do you understand that when people tell you the design causes problems, this is what they are talking about? that is why this solution is quite complex (and, by the way, the solution posted won't work for any zip codes not exactly 5 characters, or if the spacing isn't exact in each field).

Again, as I often say: when I personally find something turns out to be complicated, and others tell me there is a better and easier way to do what I am trying to do, I jump right on it and learn more. Others take it as criticism and strongly resist .... I never understand.

With a proper database design, the solution to find all duplicate ZIP codes is this:

<nothing>

In other words, no solution is needed -- the problem will NEVER occur. The database itself will not allow duplicate ZIP codes in your tables if you set it up.

Don't confuse presenting output or accepting input in 1 format with how you physcially store it in your database. You can always concatenate several rows of ZIP codes into 1 field for display purposes, if that is what you'd like to ouptut to the user. you can even ACCEPT a list of ZIP codes separated by commas from the user as well -- but you need to STORE the data properly.

A better and more proper design is to set up your table with a composite primary key of bsID/ZIP Code, so that a "bsID" can have a list of ZIP codes associated on multiple rows, and each row contains only 1 ZIP code. this allows you to quickly find, remove, change, delete and do whatever you need to a specific ZIP code or bsID instantly, without worrying about manipulating the string contents of fields in your tables.

You mention that this design has never given you any problems, yet here you are!

- Jeff
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 13:40:01
XOO2548 That is how my table is designed

More information:

I have 1 table which holds all of my service providers (members) single response information (name, address, etc,etc which adds up to 35 columns in total) stored in one table (Presently there is 6000 records in that table)Lets call the table Providers.

Then I have another table which stores up to 5 unique zip codes for each service provider which dictates where they offer their services. This table, Lets call it ZipCodes has 3 columns as already indicated.

bsID (Foreign key to the Providers table
pcMyZips (column which stores zip codes)
entryID (Primary Key which numbers each zip code entry)

Every zip code entered is associated to a bsID (presently there is over 21,000 records (zip codes) in this table.

When a visitor comes to my site looking for services, they do a search on their zip code. My present sql statement scans thru the ZipCodes table and provides the vistors with a listing of service providers which offers their services in their zipcode area. (Using the bsID relationship with the Providers table).

Now I am the first to admit that I do not know much about database design and especially working with SQL, but from my understanding this was good design. My biggest mistake was I didn't incorporate client side/server side validation, to eliminate the possibility of having one of my service providers enter in the same zip code twice. If I did i certainly wouldn't be facing this problem.

The skills of the folks on this board (and generally the answers) far outstrips my knowledge level. Presently there are very few duplicates with the same bsID in the ZipCodes table. I was hoping for a simple select statement which I could cut/paste/run that would display which bsID records have duplicate zip codes in the pcMyZips column and to list the 5 zip codes which are associated to that bsID as mentioned above.( BsID 2300, pcMyZips 90298, 90256, 90245, 90210, 90210)

I'm not upset, nor am I trying to upset anyone!

Cheers
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-11-16 : 14:07:25
show us some sample data from your ZIP codes table -- I want to make sure we understand 100% how you are storing data in there.



- Jeff
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 14:52:20
Hi Jeff,

Following is a sampling:

EntryID bsID pcMyZips
1000 3050 75034
1001 3050 75038
1002 3050 75027
1003 1630 25347
1004 1630 25336
1005 1630 25275
1006 1630 25269
1007 1630 25310
1008 2300 90298
1009 2300 90256
1010 2300 90245
1011 2300 90210
1012 2300 90210 (duplicate)
1013 4265 11010
1014 4265 11016


Thanks, Rob
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-11-16 : 14:57:03
quote:
Originally posted by robertk53

XOO2548 That is how my table is designed


If you're refering to my DDL, the zip code colum is char(5) and would only allow one zip per row. BUT It will allow you as many as you like.
quote:

but from my understanding this was good design.


Gotta disagree with that

quote:
My biggest mistake was I didn't incorporate client side/server side validation, to eliminate the possibility of having one of my service providers enter in the same zip code twice.


Gotta disagree with that as well...your biggest problem is that your table is denormalized and allows this problem.

quote:

If I did i certainly wouldn't be facing this problem.


Maybe...nothing to say that the validation in the code at 1 particular point will be remeber in some other place in the code. The Rule that says that you can't have more than 1 of the same zip belongs at the table level.

quote:
I was hoping for a simple select statement which I could cut/paste/run that would display which bsID records have duplicate zip codes in the pcMyZips column and to list the 5 zip codes which are associated to that bsID as mentioned above.( BsID 2300, pcMyZips 90298, 90256, 90245, 90210, 90210)


I'm afraid not

quote:

I'm not upset, nor am I trying to upset anyone!


Cool.

Let me ask this...how hard would it be to modify your process to look for many rows...(Hell we could even show you how to read 1) and the write multiple rows?



Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-11-16 : 15:06:30
Ok, now I'm really confused...

Is this what you want?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(EntryID int, bsID int, pcMyZips char(5))
GO

INSERT INTO myTable99(EntryID, bsID, pcMyZips)
SELECT 1000,       3050,      '75034' UNION ALL
SELECT 1001,       3050,      '75038' UNION ALL
SELECT 1002,       3050,      '75027' UNION ALL
SELECT 1003,       1630,      '25347' UNION ALL
SELECT 1004,       1630,      '25336' UNION ALL
SELECT 1005,       1630,      '25275' UNION ALL
SELECT 1006,       1630,      '25269' UNION ALL
SELECT 1007,       1630,      '25310' UNION ALL
SELECT 1008,       2300,      '90298' UNION ALL
SELECT 1009,       2300,      '90256' UNION ALL
SELECT 1010,       2300,      '90245' UNION ALL
SELECT 1011,       2300,      '90210' UNION ALL
SELECT 1012,       2300,      '90210' UNION ALL
SELECT 1013,       4265,      '11010' UNION ALL
SELECT 1014,       4265,      '11016'
GO

SELECT * 
  FROM myTable99 o 
 WHERE EXISTS ( SELECT bsid, pcMyZips 
		  FROM myTable99 i 
		 WHERE o.bsid = i.bsid AND o.PCMyZips = i.PCMyZips 
	      GROUP BY bsid, pcMyZips
		HAVING COUNT(*) > 1)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO




Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-11-16 : 15:26:16
robert -- look at your first description of your table, what column you indicated was the primary key, and how you described the output desired. and then look at what you finally described to us as how the table actually is ... completely different...

very frustrating!

- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-11-16 : 15:53:54
Ya know...just what I was looking for...another reason to have a drink

Cheers!

Speaking of which...if anyone of you go to beantown...skip the Cheers tourist trap



Brett

8-)
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 16:12:27
Hi Brett,

The pcMyZips column is a (char)5 and allow only one zip code entry per line.

Your last line "Let me ask this...how hard would it be to modify your process to look for many rows...(Hell we could even show you how to read 1) and the write multiple rows?" has me lost Brett.


I guess I'm a little shocked that it's impossible to construct an SQL
statement which would first look for any entries in pcMyZips which have they same value. (i.e. 90210 is found 300 times) and then a sub query would look through and separate the ones which have the same bsID.

Rob
Go to Top of Page

robertk53
Starting Member

9 Posts
Posted - 2004-11-16 : 16:16:18
I apologize Jeff,

I thought it wouldn't be that diffcult, so I just tried to simplify it when I originally posted the table breakdown.

8-(
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-11-16 : 16:18:01
but I gave you the query...

just cut and paste the code in to QA...it'll run no problem...

You can then just modify what I gave you to fit your tables...



Brett

8-)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site