Hi there,
The two queries below should return the primary and the secondary
institution roles when run on mssql.
--primary institution roles
select u.USER_ID,i.role_name
from users u
inner join INSTITUTION_ROLES i on u.INSTITUTION_ROLES_PK1=i.pk1
order by i.role_name
--secondary institution roles
select u.USER_ID,i.role_name
from users u
inner join USER_ROLES ur on u.pk1=ur.USERS_PK1
inner join INSTITUTION_ROLES i on ur.INSTITUTION_ROLES_PK1=i.pk1
order by i.role_name
I hope this helps.
Many thanks,
Peter Langley
Swansea University
|