SKK*


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!!!




Re: Joining two tables with repeating rows

Rob Farley


The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Hope this works for you!

Rob






Re: Joining two tables with repeating rows

Louis Davidson

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId








Re: Joining two tables with repeating rows

Louis Davidson

Apparently I worked on my solution for at least 15 minutes :)




Re: Joining two tables with repeating rows

Rob Farley

15 minutes Really





Re: Joining two tables with repeating rows

SKK*

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!





Re: Joining two tables with repeating rows

kaushuk

You try to find out in

www.sqlzoo.com