t-sql join query for a view

Question

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

       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.

For for roleid 4 it is RoleEnabled as 1? It should be 0 rite? — Kannan Kandasamy
– Kannan Kandasamy, Commented May 9, 2017 at 13:40
You can use order by in a subquery if you also use top 1 — Tab Alleman
– Tab Alleman, Commented May 9, 2017 at 13:42
for each role that is enabled or disabled, a new record is created and that record has 1 for enabled or 2 for disabled depending on what was done. — Ahmad Tijani
– Ahmad Tijani, Commented May 9, 2017 at 13:43
What is the column for the role? What is the column in the history table for the user? And how does role 4 have enabled set to 1? The only "4" in the sample history has enabled as 0. — Gordon Linoff
– Gordon Linoff, Commented May 9, 2017 at 13:44
How does user_roles_hst store the history without a date? Are you just assuming that the largest value of user_roles_hst.id must have been added last? — Bacon Bits
– Bacon Bits, Commented May 9, 2017 at 13:44

sepupic · Accepted Answer · 2017-05-09 13:56:10Z

1

    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;

answered May 9, 2017 at 13:56

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmad Tijani Over a year ago

i like this one too, outer_apply handles record in user_roles but not in user_roles_hst

Peter · Accepted Answer · 2017-05-09 14:07:41Z

1

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

edited May 9, 2017 at 14:07

answered May 9, 2017 at 13:51

Peter

8506 silver badges16 bronze badges

Comments

JNevill · Accepted Answer · 2017-05-09 14:05:10Z

0

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);

edited May 9, 2017 at 14:05

answered May 9, 2017 at 13:41

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-05-09 23:37:09Z

0

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;

edited May 9, 2017 at 23:37

answered May 9, 2017 at 13:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Ahmad Tijani Over a year ago

this only gave me one record and I'm expecting exactly 4, i.e. all the records in user_roles

Collectives™ on Stack Overflow

t-sql join query for a view

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related