----- Original Message -----
From: "David T." <sunfish62@stripped>
To: <mysql@stripped>
Sent: Friday, September 17, 2004 2:33 PM
Subject: Problems with Left Join query
> 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?
Have you tried this query as a RIGHT JOIN instead of a LEFT JOIN? If so,
what happened?
Also, what MySQL version are you using? Perhaps you are doing Left Joins on
a version that doesn't support them. I would have thought you'd get a syntax
error in that case but maybe that's too optimistic....
Rhino
