组合两个查询

Question

我有以下两个查询，它们按预期工作：

declare @user varchar(3)
declare @phrase varchar(255)

set @user = 'xyz'
set @phrase = '%nav%'

select ug.*, wap.*, w.*
from user_groups ug
inner join widget_access_permissions wap on ug.id = wap.allowed
inner join widgets w on wap.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase

select w.widget_id, ug.*, wdp.*, w.*
from user_groups ug
inner join widget_deny_permissions wdp on ug.id = wdp.denied
inner join widgets w on wdp.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase

第一个查询根据提供的@短语显示@user有权访问的任何小部件的记录。第二个查询根据提供的@短语显示@user无权访问的任何小部件的记录。

我如何将这两个查询合并为1，以便如果某人拥有基于某个组的访问权限，但因为他所在的另一个组在deny表中被拒绝，所以他无法看到结果？

我不知道我的最后一句话是否有意义。

编辑:增加了表名、表列和一些示例数据：

user_groups

table
id
display_value
employee_code
dept
persnl_typ_code

sample row 1
1000
group 1
xyz
i.t.
gen

sample row 2
1008
group 2
xyz
i.t.
gen

==========

widget_access_permissions

table
id
widget_id
allowed

sample row
0
0
1000

==========

widget_deny_permissons

table
id
widget_id
denied

sample row
0
0
1008

==========

widgets

table
widget_id
widget_name
widget_description
widget_header
widget_sub_header
widget_content

sample row
0
widget name goes here
widget description goes here
widget header goes here
widget sub header goes here
widget content goes here

Answer 1

由于您使用的是SQL Server2005，因此可以使用EXCEPT：

select w.*
from user_groups ug
inner join widget_access_permissions wap on ug.id = wap.allowed
inner join widgets w on wap.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase
EXCEPT
select w.*
from user_groups ug
inner join widget_deny_permissions wdp on ug.id = wdp.denied
inner join widgets w on wdp.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase

这将获取第一个(允许)查询的结果，减去第二个(拒绝)查询的结果，只剩下用户被允许和未被拒绝的那些小部件。

注意，我已经更改了您的原始SELECT列表，因为EXCEPT要求这两个查询具有(有效地)相同的SELECT列表，而且在我看来，您感兴趣的似乎是小部件。

Answer 2

简单的方法是使用子查询：

declare @user varchar(3)
declare @phrase varchar(255)

set @user = 'xyz'
set @phrase = '%nav%'

select ug.*, wap.*, w.*
from user_groups ug
inner join widget_access_permissions wap on ug.id = wap.allowed
inner join widgets w on wap.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase
and w.WidgetId not in (

select w.widget_id
from user_groups ug
inner join widget_deny_permissions wdp on ug.id = wdp.denied
inner join widgets w on wdp.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase
)

不过，我不确定这方面的表现如何。

编辑：

我认为您可以通过使用以下命令来删除子查询的需要：

declare @user varchar(3)
declare @phrase varchar(255)

set @user = 'xyz'
set @phrase = '%nav%'

select ug.*, wap.*, w.*
from user_groups ug
inner join widget_access_permissions wap on ug.id = wap.allowed
inner join widgets w on wap.widget_id = w.widget_id

Left Join widget_deny_permissions wdp on ug.id = wdp.denied
                        and w.widget_id = wdp.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase
and wdp.Id Is Null

这取决于您的方案和数据

Answer 3

如果我没理解错的话，你想联合权限(最严格的方法)，如果一个用户在一个组中被允许，而在另一个组中被拒绝，他应该得到那个小部件，如果是这样，你可以这样做，

declare @user varchar(3)
declare @phrase varchar(255)

set @user = 'xyz'
set @phrase = '%nav%'

select ug.*, wap.*, w.*
from user_groups ug
inner join widget_access_permissions wap on ug.id = wap.allowed
inner join widgets w on wap.widget_id = w.widget_id
where ug.employee_code = @user
and w.widget_name like @phrase
and w.widget_id NOT IN (
       select w.widget_id 
         from user_groups ug
         inner join widget_deny_permissions wdp on ug.id = wdp.denied
         inner join widgets w on wdp.widget_id = w.widget_id
         where ug.employee_code = @user
         and w.widget_name like @phrase
        )