Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.
UserAccess table:
UserID ModuleID AccessLevel
User1 1 1
User1 2 1
User2 1 1
Modules table:
ModuleID Description
1 Mod1
2 Mod2
3 Mod3
4 Mod4
5 Mod5
What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:
UserID ModuleID AccessLevel
User1 1 1
User1 2 1
User1 3 Null
User1 4 Null
User1 5 Null
User2 1 1
User2 2 Null
User2 3 Null
User2 4 Null
User2 5 Null
I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible Thanks!!!