sql join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql join

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2013-03-26 : 12:13:42

Hi,

I have two tables
Menu
----
Id : bigint
MenuId : bigint
MenuName : nvarchar(100)

Role
----
Id : bigint
RoleId : nvarchar(20)
MenuId : biging (references Menu Table)
ReadRole : bit
WriteRole : bit
DeleteRole : bit

I would require to display the list of records based on the following format and conditions

format:
MenuName ReadRole WriteRole DeleteRole
------------------------------------------------

------------------------------------------------

conditions:
1) display the matching rows from role table for the given roleid
2) display the non-existing rows from role table for given roleid

Example :
For the given roleId='R001' matching rows(first two rows), remaining rows are non-matching rows (ie) For this role remaining menus from Menu table

MenuName ReadRole WriteRole DeleteRole
------------------------------------------------
Home True True True
Inventory True False False

HRMS NULL NULL NULL
Accounts NULL NULL NULL
-
-
-
-----------------------------------------------

Thanks for your help.
Regards
SSM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-26 : 14:28:32

something like


SELECT m.MenuName,r.ReadRole,r.WriteRole,r.DeleteRole
FROM Menu m
LEFT JOIN Role r
ON r.MenuId = m.MenuId
AND r.RoleId='R001'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources