none
show all Data from Permission Table

    Question

  • I have 2 tables like that:

    PermissionsTbl
     
    PermissionID int NotNull 
    PermissionDescription nvarchar(100) NotNull
     
    PermissionID PermissionDescription 
    1 Human Resources 
    2 Employees Data 
    3 Departements
     
    ActivePermissionsTbl
     
    ActivePermID bigint NotNull 
    PermissionID int    NotNull 
    UserID       int    NotNull 
    PageActive   bit    NotNull
     
    ActivePermID PermissionID UserID   PageActive
    1            1            1        True 
    2            2            1        True 
    3            3            2        True
     
    what I want is show data like that:
     
    PermissionID      PermissionDescription     PageActive     UserID
    1                 Human Resources           True           1 
    2                 Employees Data            True           1 
    3                 Departements                             1 
    1                 Human Resources                          2 
    2                 Employees Data                           2 
    3                 Departements              True           2

    I try several methods of Join , but I failed< any suggestion please. Thanks.

    Thursday, July 04, 2013 10:44 PM

Answers

  • Declare @PermissionsTbl Table(
      PermissionID int Not Null, 
      PermissionDescription nvarchar(100) Not Null);
    Insert @PermissionsTbl(PermissionID, PermissionDescription) Values
    (1, 'Human Resources'), 
    (2, 'Employees Data'), 
    (3, 'Departements');
     
    Declare @ActivePermissionsTbl Table(
      ActivePermID bigint Not Null, 
      PermissionID int    Not Null, 
      UserID       int    Not Null, 
      PageActive   bit    Not Null);
    Insert @ActivePermissionsTbl(
      ActivePermID, 
      PermissionID, 
      UserID, 
      PageActive) Values
    (1,            1,            1,        'True'), 
    (2,            2,            1,        'True'), 
    (3,            3,            2,        'True');
    
    ;With AllUsers As
    (Select Distinct UserID From @ActivePermissionsTbl)
    Select p.PermissionID,
       p.PermissionDescription, 
       Case When ap.PageActive = 1 Then 'True' Else '' End As PageActive, 
       u.UserID
    From AllUsers u
    Cross Join @PermissionsTbl p
    Left Join @ActivePermissionsTbl ap On u.UserID = ap.UserID And p.PermissionID = ap.PermissionID
    Order By u.UserID, p.PermissionID;

    If you have a table of Users, you probably want to use it instead of the AllUsers cte to get your list of UserID's.

    Tom

    Friday, July 05, 2013 3:14 AM

All replies

  • Declare @PermissionsTbl Table(
      PermissionID int Not Null, 
      PermissionDescription nvarchar(100) Not Null);
    Insert @PermissionsTbl(PermissionID, PermissionDescription) Values
    (1, 'Human Resources'), 
    (2, 'Employees Data'), 
    (3, 'Departements');
     
    Declare @ActivePermissionsTbl Table(
      ActivePermID bigint Not Null, 
      PermissionID int    Not Null, 
      UserID       int    Not Null, 
      PageActive   bit    Not Null);
    Insert @ActivePermissionsTbl(
      ActivePermID, 
      PermissionID, 
      UserID, 
      PageActive) Values
    (1,            1,            1,        'True'), 
    (2,            2,            1,        'True'), 
    (3,            3,            2,        'True');
    
    ;With AllUsers As
    (Select Distinct UserID From @ActivePermissionsTbl)
    Select p.PermissionID,
       p.PermissionDescription, 
       Case When ap.PageActive = 1 Then 'True' Else '' End As PageActive, 
       u.UserID
    From AllUsers u
    Cross Join @PermissionsTbl p
    Left Join @ActivePermissionsTbl ap On u.UserID = ap.UserID And p.PermissionID = ap.PermissionID
    Order By u.UserID, p.PermissionID;

    If you have a table of Users, you probably want to use it instead of the AllUsers cte to get your list of UserID's.

    Tom

    Friday, July 05, 2013 3:14 AM
  • Try this,

    ---------------------------- DDL
    DECLARE @PERMISSIONTBL TABLE (PERMISSIONID INT, PERMISSIONDESCRIPTION NVARCHAR(100))
    INSERT INTO @PERMISSIONTBL VALUES(1,'HUMAN RESOURCE')
    INSERT INTO @PERMISSIONTBL VALUES(2,'EMPLOYEE DATA')
    INSERT INTO @PERMISSIONTBL VALUES(3,'DEPARTMENTS')
    DECLARE @ACTIVEPERMISSIONTBL TABLE(ACTIVEPERMID BIGINT,PERMISSIONID INT,USERID INT,PAGEACTIVE BIT)
    INSERT INTO @ACTIVEPERMISSIONTBL VALUES(1,1,1,1)
    INSERT INTO @ACTIVEPERMISSIONTBL VALUES(2,2,1,1)
    INSERT INTO @ACTIVEPERMISSIONTBL VALUES(3,3,2,1)
    ----------------------------- FINAL QUERY
    DECLARE @MAXID INT,@COUNTID INT
    DECLARE @TEMP TABLE(PERMISSIONID INT, PERMISSIONDESCRIPTION NVARCHAR(100),USERID INT,PAGEACTIVE BIT)
    SET @COUNTID=1
    SELECT @MAXID=MAX(USERID) FROM @ACTIVEPERMISSIONTBL
    WHILE @COUNTID <= @MAXID
    	BEGIN
    		INSERT INTO @TEMP
    		SELECT PERMISSIONID,PERMISSIONDESCRIPTION,@COUNTID AS USERID,
    				(SELECT PAGEACTIVE FROM @ACTIVEPERMISSIONTBL A 
    					WHERE @COUNTID=A.USERID AND A.PERMISSIONID=B.PERMISSIONID) AS PAGEACTIVE 
    						FROM @PERMISSIONTBL B
    		SET @COUNTID=@COUNTID+1
    	END
    SELECT * FROM @TEMP


    Regards, RSingh


    • Edited by RSingh() Friday, July 05, 2013 3:17 AM update
    Friday, July 05, 2013 3:15 AM