Join two tables and if the value on the first table is null, but second table has values, take the values of the second table

I have users and groups. Each of them can be given permission to do something on tables (for example "SELECT", "INSERT",..). Now I want to have a table with usernames and their permissions.

So if a user has no specific permission to SELECT form tableA, but he is in the group IT, which has permission to SELECT from that tableA, his permission on tableA should be SELECT instead of NULL.

SELECT

tableA

SELECT

tableA

SELECT

NULL

A user should be able to have multiple rights.

If a user has at least one right (no matter if specific given to user or group) it should display all rights

If a user has no rights, it should display NULL

Here are my example tables:

MEWOSTFL

select/insert

MEWOGEIM

IT

insert

MEWOWAD

IT

select

MEWOOZF

IT

MEWOBIA

IT

IT

select

One table with following inputs:

MEWOSTFL

MEWOGEIM

insert/select

MEWOWAD

MEWOOZF

select

MEWOBIA

NULL

UNION both tables already gets me close but there are duplicates.

3 Answers
3

You can use coalesce() function :

coalesce()

select coalesce(t1.permission_name, t2.permission_name) as permission_name ,
t1.name
from table2 t2 left join
table1 t1
on t1.name = t2.username;


For your current attempt i didn't find any P alise.

P

EDIT : Perhaps you need FULL OUTER JOIN but your expected output doesn't suggests this.

FULL OUTER JOIN

select coalesce(t1.permission_name, t2.permission_name) as permission_name ,
t1.name
from table1 t1 full outer join
table2 t2
on t1.name = t2.username;


EDIT : I guess you want row_number function :

with t as (
select permission_name, name
from table1
union all
select permission_name, username
from table1
)

select top (1) with ties *
from t
order by row_number() over (partition by name, permission_name order by name);


I guess this satisfies your conditions:

SELECT
permission_name
, name
FROM
(
SELECT
coalesce(t2.permission_name, t1.permission_name) as permission_name
, t1.name AS name
FROM
table1
LEFT JOIN table2
ON t1.name = t2.username
) AS _temp
WHERE
NOT _temp.permission_name IS NULL


I assume you don't want rows where t1.permission_name is null and t2.permission_name is also NULL or even t2 doesn't have a row for this entry - hence the outer query (_temp). If you don't need that just remove the outer query entirely.

I think you were very close with your original query. I have debugged your code and found that in the case statment you had a few error.

case P.permission_name
when NULL then RolePermissionMember.permission_name
else P.permission_name end as permission_name,
name

should be:

CASE WHEN RolePermissionMember.PermissionName IS NULL THEN
P.PermissionName ELSE RolePermissionMember.PermissionName END as
PermissionName, DName

I have modified it. I also brought in the two columns so you can see the effect.

DECLARE @TABLE1 TABLE(PermissionName varchar(20), DName varchar(20))
INSERT INTO @TABLE1
SELECT 'INSERT', 'MEWOSTFL' UNION ALL
SELECT 'SELECT', 'MEWOSTFL' UNION ALL
SELECT NULL, 'MEWOBIA' UNION ALL
SELECT 'SELECT', 'MEWOWAD' UNION ALL
SELECT NULL, 'MEWOGEIM' UNION ALL
SELECT NULL, 'MEWOOZF'



DECLARE @TABLE2 TABLE(PermissionName varchar(20), UserName varchar(20))
INSERT INTO @TABLE2
SELECT 'SELECT', 'MEWOWAD' UNION ALL
SELECT 'SELECT', 'MEWOGEIM' UNION ALL
SELECT 'SELECT', 'MEWOOZF'




SELECT
RolePermissionMember.PermissionName,
P.PermissionName,
CASE
WHEN RolePermissionMember.PermissionName IS NULL THEN P.PermissionName
ELSE RolePermissionMember.PermissionName END as PermissionName,
DName
FROM @TABLE1 RolePermissionMember
LEFT JOIN @TABLE2 P ON
UserName = DName


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.