|
|
Posted by MGFoster on 07/06/06 20:56
@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, 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 <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+
-----END PGP SIGNATURE-----
Navigation:
[Reply to this message]
|