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@.sh wrote:
Quote:
Originally Posted by
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?
--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 <atearthlink <decimal-pointnet
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRK15H4echKqOuFEgEQJqNgCePdnBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZhjJ5pZ1UFV
=kzX+
--END PGP SIGNATURE--|||@.sh (spam@.spam.com) writes:
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
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
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.|||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.comwrote in message
news:tOerg.596$vO.300@.newsread4.news.pas.earthlink .net...
Quote:
Originally Posted by
>
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 <atearthlink <decimal-pointnet
Oakland, CA (USA)
>
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
>
iQA/AwUBRK15H4echKqOuFEgEQJqNgCePdnBVao0mJq2YSFXG/GubalXfhMAoNRY
UlESaxNSeKctgZhjJ5pZ1UFV
=kzX+
--END PGP SIGNATURE--
Quote:
Originally Posted by
news:Xns97F91FCEE7C9Yazorman@.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)
Cheers, @.sh|||On Fri, 7 Jul 2006 10:46:12 +0100, @.sh wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
>news:Xns97F91FCEE7C9Yazorman@.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:
http://www.sommarskog.se/arrays-in-sql.html#SQL7
--
Hugo Kornelis, SQL Server MVP|||@.sh (spam@.spam.com) writes:
Quote:
Originally Posted by
Quote:
Originally Posted by
>"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
>news:Xns97F91FCEE7C9Yazorman@.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 ',' + 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment