I have the following situation and I am not sure how best to address it. Any guidance on how to prepare the needed view would be greatly appreciated.

**I have 4 tables:**  

users (userid int, username varchar)  

roles (roleid int, rolename varchar)  

businessunit (buid int, buname varchar)  

user\_role\_map (userid, roleid, buid)

In the roles table I have a role with the id of 0 which is the "system admin" role and in the businessunit table I have an IT business unit. Any users resulting from the below query would be considered system admins and should have full access to every business unit.

```
SELECT userid FROM user_role_map WHERE roleid = 0 AND buid = 0

```

I need to build a view that shows all "non system admins" union'd to a list of every business unit and every "system admin" user. The first part is easy with the below query, but the second part is what what I am struggling with.

```sql
SELECT userid, roleid, buid FROM user_role_map WHERE roleid > 0 AND buid > 0

```

I will give some example data to help illustrate what I am trying to accomplish:

```none
users
---------------
1, "sysAdmin"  
2, "salesUser1"  
3, "serviceUser1"  
4, "manager1"  
5, "salesUser2"
6, "serviceUser2"
7, "manager2"

```

```none
roles
---------------
0, "SystemAdmin"
1, "Full"
2, "Update"
3, "Read"

```

businessunit
 ---------------
 0, "IT"
 1, "fooSales"
 2, "fooService"
 3, "barSales"
 4, "barService"

```none
user_role_map
---------------
1, 0, 0
2, 1, 1
2, 3, 3
3, 1, 2
3, 3, 4
4, 1, 1
4, 1, 2
5, 1, 3
5, 3, 1
6, 1, 4
6, 3, 3
7, 1, 2
7, 1, 4

```

Finally, i need the view to provide the following for the above sample data (note the last 4 rows):

```none
new view
---------------
2, 1, 1
2, 3, 3
3, 1, 2
3, 3, 4
4, 1, 1
4, 1, 2
5, 1, 3
5, 3, 1
6, 1, 4
6, 3, 3
7, 1, 2
7, 1, 4
1, 1, 1
1, 1, 2
1, 1, 3
1, 1, 4

```

**NOTE: the example data here only has one "System Admin" user but there could be any number of users of this type.**