I have three tables:

Users:                  Depts                   Default

| fid | uid | rights |  | fid | did | rights |  | fid | rights |

|-----+-----+--------|  |-----+-----+--------|  |-----+--------|

|  1  |  1  |   4    |  |  1  | 10  |    2   |  |  1  |   2    |

|  3  |  1  |   4    |  |  2  | 10  |    2   |  |  2  |   4    |

+--------------------+  +--------------------+  +--------------+

These define the access rights a user has to a file in the system based on their user ID and the department of which they are a member. When accessing a file the effective rights are determined in order by the default rights (if any), which are overridden by the departmental rights (if any), which are in turn overridden by the user permissions, if any.

Thus, in this example, user 1, member of department 10, has no user rights for file 2, but gains rights level 2 from his department. (Default rights of 4 don't apply because they're overridden by departmental rights)

I need to query this table structure to determine the effective rights of a user for one or more files. Initially, my only requirement was to obtain the effective rights for one file at a time which I did by assigning a priority to each of three subqueries, combining them with a UNION in order of priority, and then taking only the first result, like this:

select a.rights from 

    (select 100 as priority, rights from `Users` where `uid`=1 and `fid` = 2 union 

    select 200 as priority, rights from `Depts` where `did`=10 and `fid` = 2 union 

    select 300 as priority, rights from `Default` where `fid` = 2

            ) as a 

            order by priority asc limit 1;

Now, this may not be particularly efficient, but it has served so far.

My problem now is to extend this to retrieve a user's effective rights for more than one files. Because of the priority structure this simply can't be done with the existing query.

I should be able to use a FULL OUTER JOIN across the three tables to produce a set of rights values I can COALESCE, but MySQL doesn't support FULL OUTER JOIN

After some digging around on Stack Overflow and elsewhere I arrived at this (UID, Dept ID and File Id are illustrative only):

select u.fid as fid, u.rights as urights, d.rights as drights, def.rights as defRights 

from 

  (users as u 

    left outer join Depts as d using (fid)

    left outer join Default as def using (fid))  

    where fid in (21, 1823, 1830) and dept_id=10 and uid=1

  union

    select u.fid as fid, u.rights as urights, d.rights as drights, def.rights as defRights 

    from 

      (Depts as d

      left outer join Users as u using (fid)

      left outer join Default as def using (fid))

      where fid in (21, 1823, 1830) and dept_id=10 and uid=1

  union

    select u.fid as fid, u.rights as urights, d.rights as drights, def.rights as defRights 

    from 

      (Default as def 

      left outer join Users as u using (fid)

      left outer join Depts as d using (fid))

      where fid in (21, 1823, 1830) and dept_id=10 and uid=1

Superficially, this appeared to work, but the use of the WHERE clause is filtering the results incorrectly. From my reading it seems that using an ON clause might be the solution, but getting the syntax right has defeated me.

For our example of file #2 above I should expect to get

| fid | Users | Depts | Default |

+-----+-------+-------+---------+

|  2  | null  |   2   |    4    |

+-----+-------+-------+---------+

This should give effective rights of 2 when the three rights values are coalesced.

This query may well work for this simple example. The problem that emerged in testing is that some files that should be included weren't.

So, my question is this:

How can I write the query I have with ON clauses instead of WHERE?

As a bonus, is there a better way of achieving the same result?

As a footnote: this entire structure needs to be reviewed in time, but that time is not now. This is what I have to work with until some later development prompts us to implement something better.

You could create an inline view of all the files you are interested in by unioning them together, instead of using where in. This will maintain the outernish of the query chain

eg:

select files.fid, u.rights, d.rights, def.rights

  from (

    select 1 as fid

    union all

    select 2 as fid

    union all

    select 3 as fid

    -- all the files you are interested in

  ) files

  left join users u

    on files.fid = u.fid

      and u.uid = 2         -- put the user you are searching for here

  left join depts d

    on files.fid = d.fid

      and d.did = 10        -- put the dept you are searching for here

  left join `default` def

    on files.fid = def.fid

quick demo here