show all associated records and flag disabled

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

SQL Server 2005 Forums

Transact-SQL (2005)

show all associated records and flag disabled

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-12-01 : 07:54:56

Hi

I have 3 tables that looks like this...

[tbl_Menu]

NodeID Int
ParentNodeID Int
Text nVarchar

[tbl_MenuAccess]

CustID Int
NodeID Int
IsActive Int

[tbl_UserMenuAccess]

UID Int
NodeID Int
IsActive Int

tbl_Menu hold the actual information about the different menu's.
tbl_MenuAccess holds the all available menu's for a customer.
tbl_UserMenuAccess holds the different NodeID's that the current client have access to.

tbl_MenuAccess have 10 different rows like this..

NodeID CustID IsActive
1 1 1
2 1 0
3 1 1
4 1 1
5 1 1
6 1 1
7 1 1
8 1 1
9 1 0
10 1 1

And tbl_UserMenuAccess have these rows

UID NodeID IsActive
5 1 1

Now I would like to display all items for the selected customer (CustID = 1) and selected user (UID = 5). So even if UID = only have access to NodeID = 1 the result should display all 10 rows that are available for CustID 1, but if a record is in tbl_MenuAccess and not in tbl_UserMenuAccess then it should be flagged as disabled.

This seems like it should be an easy task but I cannot get it to work, I only get the one record that exsits in tbl_UserMenuAccess..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:07:06

[code]SELECT *,
CASE WHEN uma.NodeID IS NOT NULL THEN 'Active' ELSE 'DisAbled' END
FROM [tbl_MenuAccess] ma
LEFT JOIN [tbl_UserMenuAccess] uma
ON uma.NodeID=ma.NodeID
AND uma.UID=@UID
WHERE CustID=@CustID
[/code]

@UID,@CustID are parameters through which you pass values (5 & 1 in your case)