Hi,
this question is related to my other post earlier:
http://forums.mysql.com/read.php?125,75798,75798#msg-75798
I have the two tables `level_rights` and `additional_rights`
Besides that I have `users` and `rights`
To make the tables more readable I made the column names a bit shorter
"uid" is users_id
"rid" is rights_id
"v" is value
The table `users` for example looks like this
-------------------------------
|uid| name | pwd |
-------------------------------
| 1 | John | 4f6e3afb829ac92d |
-------------------------------
The table `level_rights` for example looks like this
-------------
|uid|rid| v |
-------------
| 1 | 1 |20 |
| 1 | 2 |yes|
| 1 | 5 |no |
-------------
`additional_rights` like this
--------------
|uid|rid| v |
--------------
| 1 | 1 |50 |
| 1 | 3 |all |
| 1 | 4 |none|
--------------
`rights` like this
------------
|rid| name |
-------------------
| 1 |DISK_QUOTA |
| 2 |DELETE_USERS |
| 3 |FILE_ACCESS |
| 4 |ROOT_PRIV |
| 5 |IS_ROOT |
-------------------
The resulting table from the query should be this:
---------------
|uid| rid | v |
-----------------------
| 1 |DISK_QUOTA |50 |
| 1 |DELETE_USERS|yes |
| 1 |FILE_ACCESS |all |
| 1 |ROOT_PRIV |none|
| 1 |IS_ROOT |no |
-----------------------
The first problem was to override the value for "DISK_QUOTA" from `level_rights` with the one from `additional_rights` (50 instead of 20). As you can see in my previous post I figured that one out.
Now here's the real problem:
How should I join the tables so that none of the rights are represented twice ("select distinct" won't do because I need to override some values)?
I thought about left joining the tables `level_rights` and `additional_rights` before I join with `rights`, but that requires that one of the tables has all the rights defined that the other one has too.
In the above example "FILE_ACCESS" and "ROOT_PRIV" would not be in the resulting table if I left joined `level_rights` with `additional_rights`.
However "DELETE_USERS" and "IS_ROOT" would not be defined if I joined the two tables the other way around.
Joining `level_rights` with `rights` and then joining `additional_rights` on that, would give the same result.
I somehow think a subselect would solve that thing, although AFAIK every subselect can be done with a single select, meaning that I haven't considere some kind of join yet.
I would really appreciate any help!!
Edited 3 time(s). Last edit at 03/15/2006 10:40AM by Anders Ingemann.