Re: Problems with Left Join query

Michael Stassen Fri, 17 Sep 2004 14:52:43 -0700

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems with Left Join query

Reply via email to