Combining two queries

Question

I have the following 2 queries which work as expected:

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

The first query shows records for any widgets which @user has access to based on the @phrase provided. The second query shows records for any widgets which @user does not have access to based on the @phrase provided.

How can I combine these 2 queries into 1 so if someone has access based on a group, but because he is in another group which is declined in the deny table, he does not get to see the results?

I don't know if my last sentence makes sense...

EDIT: Added table names, table columns and a little sample data:

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

The simple way would be to use a sub query:

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
)

Not sure how the performance would be for this though.

EDIT:

I think you may be able to remove the need of a sub query by using the following:

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

This would depend on your schema and data

Answer 2

Since you are using SQL Server 2005, you can use 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

This takes the results of the first (allow) query, and subtracts the results of the second (deny) query, leaving you with just those widgets that the the user is allowed and NOT denied.

Note I have altered your original SELECT lists, as EXCEPT requires the two queries to have (effectively) the same SELECT list, and it seems to me that what you are interested in is the widgets.

Answer 3

If I understand correctly , you want to union the permissions(most restrictive approach) whereby if a user is permitted in one group and denied in another , he should get that widget, if this is the case, You can do like this ,

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
        )