CODEWITHSUNDEEP
sqlsql-server
Pradees
Pradees

Reputation: 201

Select particular record from another table based on condition

I have two tables; its structure is given below

enter image description here

enter image description here

In the first table I am assigning menu rights based on RoleID. In some cases I want to override some additional rights to a particular user (UserID). So this second table is using for additional rights for a particular user. In this below example; RoleID=2 and MenuID=4 has no Add rights; So i have added 1 rows in second table. While fetching records I need to get that record from second table instead of third row from the first table. How make a query for this purpose?

Upvotes: 0

Views: 107

Answers (3)

Poorvi Nigotiya
Poorvi Nigotiya

Reputation: 458

Select u1.Application,u1.UserID, u1.RoleID, u1.MenuID, u1.isAdd, u1.isEdit, u1.isDelete, u1.IsView 
from User_Role_Menu_Right u1 
where u1.UserID = :userID 
union
Select r.Application,u.UserID, r.RoleID, r.MenuID, r.isAdd, r.isEdit, isDelete, IsView 
from User_Role_Menu_Right u, Role_Menu_Right r 
where 
u.UserId = :userId and u.RoleId != r.RoleID and u.MenuID != r.MenuID;

Upvotes: 0

dev8080
dev8080

Reputation: 4020

You could use union:

select * 
from role_menu_rights 
where not exists 
      (select * 
        from user_role_menu_rights 
         where RoleID = role_menu_rights.RoleID  AND MenuID = role_menu_rights.MenuID )
union all 

select Application, RoleID, MenuID, IsAdd, IsEdit, IsEdit, IsDelete, IsView 
from user_role_menu_rights

Upvotes: 0

Utsav
Utsav

Reputation: 8093

If I understand your question clearly, this is what you need.

select r.application,r.roleid,r.menuid
case when u.roleid is null and u.menuid is null 
   then r.isadd else u.isadd end 
,case when u.roleid is null and u.menuid is null 
  then r.isedit else u.isedit end
-- same case conditions for other columns.

 from role_menu_rights r
left join 
user_role_menu_rights u
on r.roleid=u.roleid
and r.menuid=u.menuid

Upvotes: 2

Related Questions