help query

  •  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?

  • 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

  • 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