SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshsmanian
Starting Member

31 Posts

Posted - 03/26/2013 :  12:13:42  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/26/2013 :  14:28:32  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000