joining two tables having the same PK with another table
Posted by: Anders Ingemann
Date: March 15, 2006 10:29AM

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.

Options: ReplyQuote


Subject
Written By
Posted
joining two tables having the same PK with another table
March 15, 2006 10:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.