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.

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, CampaignHotelIDs
1 Test Campaign 1,4,5,7,9
2 Test Campaign2 1,2

UserID, UserName, UserHotelIDAccess
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 CampaignHotelIDs IN UserHotelIDAcess'

....but that doesn't want to work, can anyone give me any ideas to get this
working within just a single SQL query?

Cheers, @sh

[quoted text, click to view]

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

Your columns "UserHotelIDAccess" and "CampaignHotelIDs" 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, CampaignHotelIDs
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, UserHotelIDAccess
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 CampaignHotelIDs = UserHotelIDAccess
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBRK15H4echKqOuFEgEQJqNgCePdnBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZhjJ5pZ1UFV
=kzX+

@sh (spam@spam.com) writes:
[quoted text, click to view]

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

[quoted text, click to view]

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@sommarskog.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

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


[quoted text, click to view]

[quoted text, click to view]

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

@sh (spam@spam.com) writes:
[quoted text, click to view]

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 ',' + UserHotelIDAcess + ',' LIKE
'%,' + CampaignHotelIDs + ',%'

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@sommarskog.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

[quoted text, click to view]

Hi @sh,

Oh, Erland certainly can help you. Just check out his site:
http://www.sommarskog.se/arrays-in-sql.html#SQL7

--