HELP QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

HELP QUERY

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2004-09-01 : 09:49:52

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?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 13:17:30

a wild gues...
maybe if u add to your where:

where ... and (sendernb <> receivernb )

Go with the flow & have fun! Else fight the flow :)

mariechristine
Starting Member

27 Posts

Posted - 2004-09-02 : 03:02:49

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 06:11:04

well we could help you faster if you posted the
- Create table script for all important tables
- sample data in Insert into ... statement
- desired result

that way we can put use it simply on our machines and provide a solution.

Go with the flow & have fun! Else fight the flow :)

mariechristine
Starting Member

27 Posts

Posted - 2004-09-02 : 07:02:31

here are my tables:

CREATE TABLE [dbo].[NSRegularEventsEvents] (
[EventBatchId] [bigint] NOT NULL ,
[EventId] [bigint] IDENTITY (1, 1) NOT NULL ,
[FormName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sender] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UserAction] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordID] [int] NOT NULL ,
[Viewed] [bit] NOT NULL ,
[SubmissionTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

cREATE TABLE [dbo].[NotifSenderReceiver] (
[Form_ID] [int] NOT NULL ,
[Sender_NB] [int] NOT NULL ,
[Receiver_NB] [int] NOT NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NotifSenderReceiver] ADD
CONSTRAINT [FK_NotifSenderReceiver_FORM] FOREIGN KEY
(
[Form_ID]
) REFERENCES [dbo].[FORM] (
[Form_ID]
),
CONSTRAINT [FK_NotifSenderReceiver_NotifRole] FOREIGN KEY
(
[Sender_NB]
) REFERENCES [dbo].[NotifRole] (
[Role_NB]
)
go

CREATE TABLE [dbo].[ROLE] (
[Role_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Role_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Department_ID] [int] NOT NULL ,
[Role_IsDeptManager] [bit] NOT NULL ,
[Role_IsPriceSectManager] [bit] NOT NULL ,
[Role_IsMonopSectManager] [bit] NOT NULL ,
[Role_IsTradeManager] [bit] NOT NULL ,
[Role_IsGenManager] [bit] NOT NULL ,
[Role_IsConsManager] [bit] NOT NULL ,
[Role_IsDataEntry] [bit] NOT NULL ,
[Role_IsAdmin] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ROLEFORM] (
[RoleForm_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Role_ID] [int] NOT NULL ,
[Form_ID] [int] NOT NULL ,
[Privilege_ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[USERS] (
[User_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[User_FullName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[User_Username] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[User_Password] [varbinary] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserRole] (
[UserRole_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[User_ID] [int] NOT NULL ,
[Role_ID] [int] NOT NULL
) ON [PRIMARY]
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 07:58:23

well sample data would be usefull...
in this format:

insert into NSRegularEventsEvents (col1, col2, ...)
select col1, col2, ... union all
select col1, col2, ... union all
select col1, col2, ...

for all the tables

and the desired result that you want from sample data.

without all that, all we can do is guess how to fix your problem.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources