Relational Division Query

Question

I Have tree table in SQL Server 2008 likes below:

UserTbl:

 UserID     UserName
 1          User1
 2          User2
 3          User3

ActivityTbl:

 ActivityID   Activity
 1            A
 2            B
 3            C

UserAccessTbl (Relational Division Table):

 UserID      Activity        Access
 1           A               True
 1           B               False
 2           C               True

I want to have a query with this result:

 UserID      Activity        Access
 1           A               True
 1           B               False
 1           C               Null
 2           A               Null
 2           B               Null
 2           C               True
 3           A               Null
 3           B               Null
 3           C               Null

Would you please help me to do that? I want to use this to perform users access setting in my .Net application. Tanks

Answer 1

1. Join UserTbl with ActivityTbl using a cross join

   Select * from UsertTbl cross join ActivityTbl

https://technet.microsoft.com/en-us/library/ms190690%28v=sql.105%29.aspx

This will result in a dataset with all the possible combinations between users and activities.

2. Join the previous dataset with UserAccessTbl using "left join".

https://technet.microsoft.com/en-us/library/ms187518%28v=sql.105%29.aspx

(NOTE: you can do it in only one query, just join the two pieces)