September 1, 2004 at 9:02 am
i have a table Events (FormID, Sender, Useraction) and another table SenderReceiver(FormID, SenderNb, ReceiverNB,Action) and tables Users, Role, UserRole, form, RoleForm UserAction can be: new, update or view Action is same as userAction Sender and SenderNb are the role nb for the person who enters/udates/views a record when a user enters or updates a record related to a certain form, the events table will be filled by trigger. For example: form id 14, sender 2, useraction 'new' The SenderREceiver table is a static table defined from the beginning showing who must send the record to whom. Example: Form id 14, sendernb 2, receivernb 4, action 'new' form id 14, sendernb 4, receivernb 2, action 'update' form id 14, sendernb 2, receivernb 1, action 'view' i want to retrieve all events table for a certain userID who is the receiver for form id 14 for example. I made a procedure for it. the problem is that When sender nb 2 is inserting a record, the proc. is returning an event for this user because he is a receiver too. It shouldn't do so. How to solve it? |
September 2, 2004 at 1:04 am
Well basicly this is my query but it isn't working. It is arriving to sender 2, 4 and 1 while it should only arrive to sender 4.
select distinct F.Form_NAme, N.Sender, N.Viewed , N.recordID, N.SubmissionTime
from Users u inner join UserRole UR on u.[user_id]=UR.[user_ID]
inner join Role R on UR.Role_ID=R.Role_ID
inner join Roleform RF on Rf.Role_ID = R.Role_ID
inner join Form F on f.Form_ID=RF.Form_ID
inner join NotifSenderReceiver SR on F.Form_ID=SR.Form_ID
inner join
ConsumerProtectionInstanceConsumerProtection..NSRegularEventsEvents N
on N.FormName = F.Form_Name
where (R.Role_IsConsManager = case when SR.Receiver_NB=1 then 1 else 0 end
or R.Role_IsDeptManager = case when SR.Receiver_NB=2 then 1 else 0 end
or R.Role_IsTradeManager = case when SR.Receiver_NB=6 then 1 else 0 end
or R.Role_IsGenManager = case when SR.Receiver_NB=5 then 1 else 0 end
or R.Role_IsPriceSectManager = case when SR.Receiver_NB=3 then 1 else 0 end
or R.Role_IsMonopSectManager = case when SR.Receiver_NB=4 then 1 else 0 end )
and U.[User_ID]=@userID
and N.UserAction = SR.[Action] and N.Sender <>SR.Receiver_NB
order by N.SubmissionTime, N.Viewed desc
September 2, 2004 at 1:06 am
Well basicly this is my query but it isn't working. It is arriving to sender 2, 4 and 1 while it should only arrive to sender 4. select distinct F.Form_NAme, N.Sender, N.Viewed , N.recordID, N.SubmissionTime from Users u inner join UserRole UR on u.[user_id]=UR.[user_ID] inner join Role R on UR.Role_ID=R.Role_ID inner join Roleform RF on Rf.Role_ID = R.Role_ID inner join Form F on f.Form_ID=RF.Form_ID inner join NotifSenderReceiver SR on F.Form_ID=SR.Form_ID inner join ConsumerProtectionInstanceConsumerProtection..NSRegularEventsEvents N on N.FormName = F.Form_Name
where (R.Role_IsConsManager = case when SR.Receiver_NB=1 then 1 else 0 end or R.Role_IsDeptManager = case when SR.Receiver_NB=2 then 1 else 0 end or R.Role_IsTradeManager = case when SR.Receiver_NB=6 then 1 else 0 end or R.Role_IsGenManager = case when SR.Receiver_NB=5 then 1 else 0 end or R.Role_IsPriceSectManager = case when SR.Receiver_NB=3 then 1 else 0 end or R.Role_IsMonopSectManager = case when SR.Receiver_NB=4 then 1 else 0 end ) and U.[User_ID]=@userID and N.UserAction = SR.[Action] and N.Sender <>SR.Receiver_NB order by N.SubmissionTime, N.Viewed desc |
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply