I have two tables; its structure is given below
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?
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