First, you want to get an answer for every module, so you must use
modules LEFT JOIN permissions
to get a row for every module. You see? The table with missing rows (no
row for Bob/module3 in permissions) goes on the right.
When the table on the right does not have a matching row, the LEFT JOIN
fills the result with NULLs in place of the missing values from the table on
the right. So, given your sample data,
SELECT m.mod_name, p.*
FROM modules m
LEFT JOIN permissions p ON m.mod_name = p.module
would yield
mod_name | ID | USER | MODULE | ADD | MOD | DEL
--------------------------------------
module1 | 1 | bob | module11 | 1 | 0 | 1
module2 | 2 | bob | module2 | 1 | 0 | 1
module3 |NULL | NULL | NULL |NULL |NULL |NULL
module1 | 3 | jane | module1 | 1 | 1 | 0
...
Now, do you see the problem with adding "WHERE p.user='bob'"? That won't
match the 3rd row, which was meant to show bob has no permissions on
module3. So, you have to make that part of the join condition.
Try this:
SELECT m.mod_name, p.ADD, p.MOD, p.DEL
FROM modules m
LEFT JOIN permissions p
ON m.mod_name = p.module AND p.user='bob';
You probably want to see 0 instead of NULL for the missing rows. Then use:
SELECT m.mod_name,
IFNULL(p.ADD,0) Add, IFNULL(p.MOD,0) Mod, IFNULL(p.DEL,0) Del
FROM modules m
LEFT JOIN permissions p
ON m.mod_name = p.module AND p.user='bob';
Finally, you are wasting a lot of space by storing module names in the
permissions table. I expect your modules table has an id column. (It
should.) You should store that id in the permissions table, rather than the
name. That will save space and speed up your query. Then you would use
something like:
SELECT m.mod_name,
IFNULL(p.ADD,0) Add, IFNULL(p.MOD,0) Mod, IFNULL(p.DEL,0) Del
FROM modules m
LEFT JOIN permissions p
ON m.id = p.module_id AND p.user='bob';
Michael
David T. wrote:
> I am building a database with permissions for different modules. The
> permissions table contains a separate row for each module/user combination, and
> lists the add/mod/del permissions for that module. So, for example, user 'bob'
> might have add and del rights for 'module1', add only rights for 'module2', and
> no rights for 'module3'. The rows in permissions would be:
>
> ID | USER | MODULE | ADD | MOD | DEL
> ----------------------------------------
> 1 | bob | module1 | 1 | 0 | 1
> 2 | bob | module2 | 1 | 0 | 0
>
> But, I need to build an entry form that lists all of the modules in the modules
> table and loads in the permissions for that user. I was believing that I could
> write a single LEFT JOIN query that would give me all the modules and
> permissions in one single pass. However, when I build the query, it only
> returns the user records. I have tried:
>
> SELECT modules.mod_name, permissions.*
> FROM permissions
> LEFT JOIN modules ON
> permissions.module = modules.mod_name
> WHERE permissions.user='bob'
>
> What am I doing wrong?
>
> Cheers,
> David
