Can I do this? - SQL Server Forums

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)

Can I do this?

Author

Topic

bglodde
Starting Member

25 Posts

Posted - 2002-01-23 : 04:15:56

Is it possible to do a join where an integer is IN a CSV?

SELECT * FROM tblCities
INNER JOIN tblZipCodes ON tblZipCodes.zip_id IN (tblCities.city_zipcode)

where tblCities.city_zipcode is the csv list.

Can anyone suggest the proper way to go about solving this type of problem? What I really need is just the value for the csv's in my return recordset. Should there be several queries that get magically merged into one? This one is really holding me up...HELP!!!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-23 : 04:19:36

Try looking at the faq's.
Yes you can do it but not that way either dsql or a like statement.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

bglodde
Starting Member

25 Posts

Posted - 2002-01-23 : 12:10:44

Can someone give me something a little more specific than "you CAN do this"? Maybe a pointer to something on this site...I have searched the FAQs and cannot find anything that will illustrate how it will be possible to use DSQL to allow this type of JOIN!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 12:17:45

I'm not too sure that it can be done, when I think about it. Even if you can do it, it will be horribly inefficient; seriously, the performance will absoutely suck. Can you change the table structures so that each city and each zip code are stored as separate rows? Something like this:

City    ZipCode
NYC     10001
NYC     10035
NYC     10017

This is a much better structure both practically and logically.

Edited by - robvolk on 01/23/2002 12:18:58

bglodde
Starting Member

25 Posts

Posted - 2002-01-23 : 12:21:59

Thanks Rob, I'll try that

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-23 : 18:14:06

how about

SELECT *
FROM tblCities, tblZipCodes
where ',' + tblCities.city_zipcode + ',' like '%,' + tblZipCodes.zip_id + ',%'

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources