Compare data & populate in the same table

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

General SQL Server Forums

New to SQL Server Programming

Compare data & populate in the same table

Author

Topic

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-10 : 07:36:22

Hi,

I have the data as below,in which owner is assigning some task to another user.

INPUT
#########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 1 1 user2
user1 1 2 user3
user1 1 3 user4

PRIVILEGE table
#########

USER_DETAIL PRIVILEGE_ID

user1 10
user1 11
user1 12
user2 8
user3 5
user4 6

OWNER has one set of privilege details in privilege table.

I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want
to populate the data output as below

NEEDED OUTPUT
###########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 1 1 user2
user1 1 2 user3
user1 1 3 user4
user2 1 1 user2
user3 1 2 user3
user4 1 3 user4

I am populating this data in the view. Please let know your inputs.

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-10 : 09:57:31

This doesn't make sense. The privilege table has user1 three times. Which privilege_d should be used (10,11, or 12) and why?

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-11 : 02:37:19

quote:
Originally posted by gbritton

This doesn't make sense. The privilege table has user1 three times. Which privilege_d should be used (10,11, or 12) and why?

Hi gbritton,

We populate the INPUT data in the view for report. I want to populate the data in the view by using the UNION ALL for the user privilege not matching with the below condition,

Presently in view, we getting the data only the privilege id available in the privilege table ie.. user2 doesn't have the privilege for 10,11,12, so the data won't populate in the view. But, user1 can assign the task to user2.

I want to check the privileges of the user2 and user 1, if it doesn't match. I want to create the entry as shown in the NEEDED OUTPUT.

Does it make sense. Please let me know if you get any idea to implement it and also performance of the view should be affected much. Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-11 : 06:57:10

do you mean match any of the privileges of user 1? Since in your sample data user 1 has three privileges should I be checking the other users see if they have any of those privileges?

rsmohankumar
Starting Member

11 Posts

Posted - 2015-02-11 : 07:29:03

quote:
Originally posted by gbritton

do you mean match any of the privileges of user 1? Since in your sample data user 1 has three privileges should I be checking the other users see if they have any of those privileges?

I mean if the user2 privilege is not match with all three privileges of the user1, then we need to populate the row as i mentioned in the NEEDED OUTPUT.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-11 : 08:44:15

I worked up the solution below. However it does not match your desired output since there are no tasks owned by user2, user3 or user4. For example, why are you showing user2 as the owner of ID=1? The input data does not have that.


--INPUT

declare @input table ([OWNER] varchar(10), ID int,  TASK_ID int,  TASK_ASSIGNEE varchar(10))
insert into @input ([OWNER], ID, TASK_ID, TASK_ASSIGNEE) values

('user1', 1,1,'user2'),
('user1', 1,2,'user3'),
('user1', 1,3,'user4')

--PRIVILEGE table

declare @privilege table (USER_DETAIL varchar(10),  PRIVILEGE_ID int)
insert into @privilege (USER_DETAIL, PRIVILEGE_ID) values

('user1', 10),
('user1', 11),
('user1', 12),
('user2', 8	),
('user3', 5	),
('user4', 6	)

select distinct [OWNER], ID, TASK_ID, TASK_ASSIGNEE
from @input i
join @privilege p
  on i.task_assignee = p.user_detail
where p.PRIVILEGE_ID not in 
(
	select PRIVILEGE_ID
	from @privilege
	where USER_DETAIL = 'user1'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources