Complex Query

Need to create a SELECT statement which is above my skills. There are two
tables involved, both are shown below. Table 1 stores messages to be
delivered to a user on a web page. Messages can be sent to a specific set o
f
users by setting BlockList to false and adding each user to Table 2 or to al
l
users by setting BlockList to true and adding no users to Table 2. Messages
can be active until acknowledged (EndDate is null) or to a specific EndDate.
The query must return all active messages for a specific user name (the only
parameter). A message should be returned if BlockList is false and the user
is listed, or BlockList is true and the user is not listed. In addition, if
there is an end date it must not be passed and if BlockList is false (meanin
g
the user list was established as a list of recipients) then the Recieved dat
e
on Table 2 must be null indicating the user has not yet received the message
.
I am open to table architecture changes which might allow the same
information to be conveyed in a simpler manner if those more experienced
think that is better than writing a query to extract this information.
Table 1: IntranetMessages
_Column Name_ _Type_ _Allow Null_
MessageID (PK) uniqueidentifier No
Text nvarchar(MAX) No
URL nvarchar(MAX) No
Tooltip nvarchar(MAX) No
BlockList bit No
IssueDate DateTime No
EndDate DateTime Yes
Table 2: IntranetMessageRecipients
_Column Name_ _Type_ _Allow Null_
RecID (PK) uniqueidentifier No
MessageID (FK) uniqueidentifier No
Recipient nvarchar(MAX) No
Received datetime Yesyou'll need a reference from the IntranetMessageRecipients to the
IntranetMessages to achieve this, aka foreign key.
is this possible?

> _Column Name_ _Type_ _Allow Null_
RecID int No
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
you could insert a default value for those messages that do not have a
recipient.

> Table 2: IntranetMessageRecipients

> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes|||Sorry, I marked the MessageID field in the IntranetMessageRecipients table a
s
a Foreign Key but didn't explicitely say that it corresponds to the MessageI
D
(PK) in the IntranetMessages table. That is how it is setup, however, if
that is what you mean.
"Gerard" wrote:

> you'll need a reference from the IntranetMessageRecipients to the
> IntranetMessages to achieve this, aka foreign key.
> is this possible?
>
> RecID int No
> you could insert a default value for those messages that do not have a
> recipient.
>
>
>|||SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipient
FROM IntranetMessages im
LEFT OUTER JOIN
IntranetMessageRecipients imr
ON im.MessageID = imr.MessageID
AND imr.Received IS NULL
WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
@.passedInUser
AND im.EndDate IS NULL
A couple of thoughts on your schema. I would be careful with the use of
uniqueidentifiers as your primary keys. For several reasons:
1) I hope that you have the primary key as a clustered index. It is a good
rule of thumb to always have a clustered index
2) Uniqueidentifiers are typically bad choices as clustered indexes since
they are not ever increasing. This causes your table to become fragmented
quickly. If you use uniqueidentifiers as your clustered primary key, you
should make sure that you are rebuilding the index regularly.
Beyond that, your schema is not normalized since you could have the same
username in your intranetMessage table many times. It would make this a bit
cleaner if you had a table with all users that is referenced by the
Recipients table.
But, I think that my query should do the trick for you.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:

> Need to create a SELECT statement which is above my skills. There are two
> tables involved, both are shown below. Table 1 stores messages to be
> delivered to a user on a web page. Messages can be sent to a specific set
of
> users by setting BlockList to false and adding each user to Table 2 or to
all
> users by setting BlockList to true and adding no users to Table 2. Messag
es
> can be active until acknowledged (EndDate is null) or to a specific EndDat
e.
> The query must return all active messages for a specific user name (the on
ly
> parameter). A message should be returned if BlockList is false and the us
er
> is listed, or BlockList is true and the user is not listed. In addition,
if
> there is an end date it must not be passed and if BlockList is false (mean
ing
> the user list was established as a list of recipients) then the Recieved d
ate
> on Table 2 must be null indicating the user has not yet received the messa
ge.
> I am open to table architecture changes which might allow the same
> information to be conveyed in a simpler manner if those more experienced
> think that is better than writing a query to extract this information.
> Table 1: IntranetMessages
> _Column Name_ _Type_ _Allow Null_
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes
>
>|||sorry misunderstanding on my part
maybe something like this will work (not tested)
select * from IntranetMessages
inner join IntranetMessageRecipients on
IntranetMessageRecipients.MessageID = IntranetMessages .MessageID
where
(BlockList = 0 and IntranetMessageRecipients.RecID = @.recp) or
(BlockList = 1 and IntranetMessageRecipients.RecID <> @.recp)
regards
Gerard|||Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers... I
don't know what a clustered index is, but that's for me to look up and your
point about usernames is well taken.
"Ryan Powers" wrote:
> SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipie
nt
> FROM IntranetMessages im
> LEFT OUTER JOIN
> IntranetMessageRecipients imr
> ON im.MessageID = imr.MessageID
> AND imr.Received IS NULL
> WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
> @.passedInUser
> AND im.EndDate IS NULL
> A couple of thoughts on your schema. I would be careful with the use of
> uniqueidentifiers as your primary keys. For several reasons:
> 1) I hope that you have the primary key as a clustered index. It is a goo
d
> rule of thumb to always have a clustered index
> 2) Uniqueidentifiers are typically bad choices as clustered indexes since
> they are not ever increasing. This causes your table to become fragmented
> quickly. If you use uniqueidentifiers as your clustered primary key, you
> should make sure that you are rebuilding the index regularly.
> Beyond that, your schema is not normalized since you could have the same
> username in your intranetMessage table many times. It would make this a b
it
> cleaner if you had a table with all users that is referenced by the
> Recipients table.
> But, I think that my query should do the trick for you.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Let me know if it works.
Also, if you need me to point you to a couple of sites about Clustered
indexes and tuning, let me know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers...
I
> don't know what a clustered index is, but that's for me to look up and you
r
> point about usernames is well taken.
> "Ryan Powers" wrote:
>|||If you don't mind a few references are always helpful. I am just getting my
feet wet in SQL Server so I have a lot to learn.
Your query got me 90% of the way there and once I read up on CASE and LEFT
OUTER JOIN statements to see what was going on I got it to do what I needed.
The end result follows:
SELECT im.MessageID, im.BlockList, imr.Recipient
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON im.MessageID =
imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList =
0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 END)
END = 1)
"Ryan Powers" wrote:
> Let me know if it works.
> Also, if you need me to point you to a couple of sites about Clustered
> indexes and tuning, let me know.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Sorry, I had replaced the parameters for testing. The real version is:
SELECT im.MessageID
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON
im.MessageID = imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN
im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1)
"MKing" wrote:
> If you don't mind a few references are always helpful. I am just getting
my
> feet wet in SQL Server so I have a lot to learn.
> Your query got me 90% of the way there and once I read up on CASE and LEFT
> OUTER JOIN statements to see what was going on I got it to do what I neede
d.
> The end result follows:
> SELECT im.MessageID, im.BlockList, imr.Recipient
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON im.MessageID
=
> imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList
=
> 0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 EN
D)
> END = 1)
> "Ryan Powers" wrote:
>|||Glad you got it to work.
I was trying to figure out why what I gave you did not work right and I see
two things. Because I think you can do something a bit cleaner than your
final solution.
1) It looks like you are saying match the recipient when blocklist is 0
instead of 1. Can you explain that (just for my curiousity)? Is it true tha
t
you want to look the recipient up when BlockList = 0? Is it also true that
the message will have no rows in the Recipient table when BlockList = 1?
2) I failed to include rows with end date later than today.
As far as references go, I prefer watching presentations/ webcasts. There
is a great one by Kimberly Tripp on index best practices that would give you
a great background on clustered indexes. It is focused on SQL 2005, but the
concepts transcend the releases. You can find it here.
http://www.microsoft.com/uk/technet...aspx?videoid=29
Beyond that, here are a couple good articles.
http://www.sql-server-performance.c..._structures.asp
http://www.sql-server-performance.c...red_indexes.asp
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Sorry, I had replaced the parameters for testing. The real version is:
> SELECT im.MessageID
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON
> im.MessageID = imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN
> im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1
)
> "MKing" wrote:
>