Odd SQL IN usage?

**MGFoster** · Jul 6 '06, 08:55 PM

Re: Odd SQL IN usage?

@sh wrote:

Hi guys,
>
Got an odd SQL string that I need to produce that is most probably simple to
construct but with it being hot in our office, I simply can't get my head
around it....!!
>
Its based around an online emailing facility whereby multiple hotels can be
emailed via a single application. Users within the application have access
rights to email only specific hotels.
>
The tables are laid out like this (irrelevant columns left out)...
>
CampaignID, CampaignName, CampaignHotelID s
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2
>
UserID, UserName, UserHotelIDAcce ss
1 Test User 1,6,7
2 Test User 2,7
>
Now on the stats page I want to give users access to view ONLY sent
campaigns to which they have access to view, I was considering the IN SQL
statement to achieve something like this...
>
'WHERE CampaignHotelID s IN UserHotelIDAces s'
>
...but that doesn't want to work, can anyone give me any ideas to get this
working within just a single SQL query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your columns "UserHotelIDAcc ess" and "CampaignHotelI Ds" are in violation
of 1NF (First Normal Form) "A cell must be atomic." I.e., there must
only be one item per column. If your data was like this:

CampaignID, CampaignName, CampaignHotelID s
1 Test Campaign 1
1 Test Campaign 4
1 Test Campaign 5
1 Test Campaign 7
1 Test Campaign 9
2 Test Campaign2 1
2 Test Campaign2 2

UserID, UserName, UserHotelIDAcce ss
1 Test User 1
1 Test User 6
1 Test User 7
2 Test User 2
2 Test User 7

Your WHERE clause would work like this:

WHERE CampaignHotelID s = UserHotelIDAcce ss
--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK15H4echKq OuFEgEQJqNgCePd nBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZh jJ5pZ1UFV
=kzX+
-----END PGP SIGNATURE-----

**Erland Sommarskog** · Jul 6 '06, 10:05 PM

Re: Odd SQL IN usage?

@sh (spam@spam.com) writes:

Got an odd SQL string that I need to produce that is most probably
simple to construct but with it being hot in our office, I simply can't
get my head around it....!!

It must also have been hot in the office when this was desiged:

CampaignID, CampaignName, CampaignHotelID s
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2
>
UserID, UserName, UserHotelIDAcce ss
1 Test User 1,6,7
2 Test User 2,7

If you are on SQL 2000, this is very painful to work with. If you are on
SQL 2005, it's slightly less painful.

Before I go, which version of SQL Server do you actually have?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**--CELKO--** · Jul 6 '06, 11:05 PM

Re: Odd SQL IN usage?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You can immediately improve your database by not allowing the moron
that crammed everything into a single column to write code until he can
explain Normal Forms. I would also inspect everything he has done and
pull it out. That kind of fundamental error tells you what kind of
programmer you have.

**@sh** · Jul 7 '06, 09:35 AM

Re: Odd SQL IN usage?

Thanks for your reply, I see what you're suggesting but its too late to
change the overall structure of the table now ;o(

"MGFoster" <me@privacy.com wrote in message
news:tOerg.596$ vO.300@newsread 4.news.pas.eart hlink.net...

>
Your columns "UserHotelIDAcc ess" and "CampaignHotelI Ds" are in violation
of 1NF (First Normal Form) "A cell must be atomic." I.e., there must
only be one item per column. If your data was like this:
>
CampaignID, CampaignName, CampaignHotelID s
1 Test Campaign 1
1 Test Campaign 4
1 Test Campaign 5
1 Test Campaign 7
1 Test Campaign 9
2 Test Campaign2 1
2 Test Campaign2 2
>
UserID, UserName, UserHotelIDAcce ss
1 Test User 1
1 Test User 6
1 Test User 7
2 Test User 2
2 Test User 7
>
Your WHERE clause would work like this:
>
WHERE CampaignHotelID s = UserHotelIDAcce ss
--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)
>
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
>
iQA/AwUBRK15H4echKq OuFEgEQJqNgCePd nBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZh jJ5pZ1UFV
=kzX+
-----END PGP SIGNATURE-----

**@sh** · Jul 7 '06, 09:45 AM

Re: Odd SQL IN usage?

"Erland Sommarskog" <esquel@sommars kog.sewrote in message

news:Xns97F91FC EE7C9Yazorman@1 27.0.0.1...
It must also have been hot in the office when this was desiged:

Well it seemed a good idea at the time, and infact works within the
application itself brilliantly and efficiently without numerous hits on the
DB - however this one element now is proving tricky

We're using SQL 7 so based on what you've said, I guess you can't help ;o)

Cheers, @sh

**Hugo Kornelis** · Jul 7 '06, 09:55 PM

Re: Odd SQL IN usage?

On Fri, 7 Jul 2006 10:46:12 +0100, @sh wrote:

>"Erland Sommarskog" <esquel@sommars kog.sewrote in message
>news:Xns97F91F CEE7C9Yazorman@ 127.0.0.1...
>It must also have been hot in the office when this was desiged:

>
>Well it seemed a good idea at the time, and infact works within the
>application itself brilliantly and efficiently without numerous hits on the
>DB - however this one element now is proving tricky
>
>We're using SQL 7 so based on what you've said, I guess you can't help ;o)

Hi @sh,

Oh, Erland certainly can help you. Just check out his site:

Arrays and Lists in SQL Server (Short version)

http://www.sommarskog.se/arrays-in-sql.html#SQL7

--
Hugo Kornelis, SQL Server MVP

**Erland Sommarskog** · Jul 7 '06, 09:55 PM

Re: Odd SQL IN usage?

@sh (spam@spam.com) writes:

>"Erland Sommarskog" <esquel@sommars kog.sewrote in message
>news:Xns97F91F CEE7C9Yazorman@ 127.0.0.1...
>It must also have been hot in the office when this was desiged:

>
Well it seemed a good idea at the time, and infact works within the
application itself brilliantly and efficiently without numerous hits on
the DB - however this one element now is proving tricky
>
We're using SQL 7 so based on what you've said, I guess you can't help
;o)

SQL 7? That will not make it even less painful. :-)

There are two approaches. One is to run a cursor over the rows and
for each row call a stored procedure that unpacks the row into a
table, so that you can run the queries the proper way. You can find
examples of such procedures on my web site:
http://www.sommarskog.se/arrays-in-sql.html#SQL7.

I would in such case such suggest that you put this code in the trigger
on this table, so that you always can work on properly desinged tables.
Overall, you should strive of changing the database design to move away
from this structure. Yes, I see that you in other post said that it's
too late to change the design, but I disagree. Unless the product already
has a declared end of life, it's never too late to change a flat-out
incorrect design like this one.

The other approach is a query similar to:

WHERE ',' + UserHotelIDAces s + ',' LIKE
'%,' + CampaignHotelID s + ',%'

This theme has some variations, see
http://www.sommarskog.se/arrays-in-sql.html#realslow. All of them are
painfully slow, and could prove unworkable if your data has any volumes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Odd SQL IN usage?

Odd SQL IN usage?

Comment

Comment

Comment

Comment

Comment

Comment

Comment