Pradees Pradees - 2 months ago 11
SQL Question

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?

Answer

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