double outer join?

Question

I have what I think is a fairly simple problem, but i'm missing something.

I have 3 tables :

t_users : user_id, user_name
t_users_modules : user_id, module_id
t_modules : module_id, module_name

I'd like to output a list of all module_name, for each user_name, and check if this user indeed have this right in the t_users_modules table.

Is it a cross join ? I've never used it.

I tried with 2 left outer join without success.

Answer 1

Yes, you use a cross-join to generate all of the possibilities and then a left join to test for the presence or absence of the required row:

select module_name, user_name,
     CASE WHEN um.user_id is not null THEN 1 ELSE 0 END as HasRight
from
   t_users u
       cross join
   t_modules m
       left join
   t_user_modules um
       on
           u.user_id = um.user_id and
           m.module_id = um.module_id

Incidentally, if you're using t_ as some form of prefix to identify tables, I'd recommend dropping it - in almost all places in SQL, the type of the object is identifiable from where the object appears in the statement. The only general exception is that tables and views are indistinguishable - but that's a good thing - they shouldn't generally be distinguished between.