t-sql join query for a view

I am trying to write a query for a view but I don’t have a hold of it.
I have two tables

         user_roles
    id  user_id   role_id                
    1     1        4
    2     1        1
    3     1        2
    4     1        3

        user_roles_hst
    id  UserRolesID  RoleEnabled     creator
     1     1              1            1    
     2     1              0            1    
     3     1              1            1    
     4     2              0            1    
     5     2              1            1    
     6     3              0            1
     7     4              0            1

Now i want a view with all the user roles and their latest enabled status
like so

  • SQL Server - Simultaneous Inserts to the table from multiple clients - Check Limit and Block
  • Providing Language FallBack In A SQL Select Statement
  • Drop SQL login even while logged in
  • How to find the boundaries of groups of contiguous sequential numbers?
  • Remove Rows That Sum Zero For A Given Key
  • name of index need to be unique in database?
  •        vw_user_roles
        user_id    role_id    RoleEnabled
          1          4             1
          1          1             1
          1          2             0
          1          3             0
    

    The user_roles_hst stores history of role status changes as they are enabled or disabled but in the vw_user_roles, i need the latest status for each or the role_id

    Query:

    select * from 
    (select x.user_id, x.UserRolesID, x.RoleEnabled from 
    (select h.id, u.user_id, h.UserRolesID, h.RoleEnabled from UserRoles u, UserRoles_HST h 
    where u.id = h.UserRolesID
    group by h.id, h.UserRolesID, h.RoleEnabled, u.user_id
    ) x
    order by x.id desc
    ) y
    group by y.user_id, y.UserRolesID, y.RoleEnabled
    

    I tried the above query but then i realize i can’t use order by in a subquery

    I need help on how to get the right query.

    4 Solutions collect form web for “t-sql join query for a view”

        declare @user_roles table ( id  int, user_id int, role_id  int);
        insert into @user_roles values            
            (1,     1,        4),
            (2,     1,        1),
            (3,     1,        2),
            (4,     1,        3)
    
        declare @user_roles_hst table (id  int, UserRolesID int, RoleEnabled  int,  creator int);
        insert into @user_roles_hst values
             (1,     1,              1,            1),    
             (2,     1,              0,            1),   
             (3,     1,              1,            1),    
             (4,     2,              0,            1),    
             (5,     2,              1,            1),    
             (6,     3,              0,            1),
             (7,     4,              0,            1)
    
        select r.user_id,    
               r.role_id, 
               a.RoleEnabled
        from @user_roles r outer apply (select top 1 *
                                        from @user_roles_hst h
                                        where h.UserRolesID = r.id
                                        order by id desc) a; 
    
    Declare @user_roles table (id int, user_id int, role_id int)
    Declare @user_roles_hst table (id int, userrolesid int, roleenabled int, creator int)
    
    insert into @user_roles values
    (1,1,4),
    (2,1,1),
    (3,1,2),
    (4,1,3)
    
    insert into @user_roles_hst values
    (1, 1, 1, 1),
    (2, 1, 0, 1),
    (3, 1, 1, 1),
    (4, 2, 0, 1),
    (5, 2, 1, 1),
    (6, 3, 0, 1),
    (7, 4, 0, 1)
    
    select ur.user_id,ur.role_id,x.roleenabled
    from @user_roles ur
    join (
    Select row_number() over (partition by userrolesid order by id desc) rn,*
    from @user_roles_hst) x on ur.id = x.userrolesid
    where rn = 1
    

    The max id from vw_user_roles for each user_id:

    SELECT max(id) as maxID FROM user_roles_hst GROUP BY userRolesID;
    

    Using that to get the records from your tables for each maxID:

    SELECT t2.user_id, t1.UserRolesID, t1.rolesEnabled
    FROM user_roles_hst t1
        INNER JOIN user_roles t2 ON
            t1.userRolesID = t2.role_id
    WHERE id IN (SELECT max(id) as maxID FROM user_roles_hst GROUP BY userRolesID);
    

    I am assuming:

    • creator is really the user.
    • userRoleId is the user role
    • By latest, you mean “biggest id”

    Then you can use row_number():

    select urh.creator as user_id, userRoleId, as role_id, role_enabled as enabled
    from (select urh.*,
                 row_number() over (partition by creator, userRoleId order by id desc) as seqnum
          from user_roles_hst urh
         ) urh
    where seqnum = 1;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.