God of Source God of Source - 2 months ago 6
SQL Question

SQL add relations to relation table

I have 3 tables which manage the users an their rights

Employee
+-----------+------+
|EmployeeID | Name |
+-----------+------+
|1 |Tim |
|2 |Tom |
+-----------+------+

Right
+-----------+------+
|RightID |Name |
+-----------+------+
|1 |Read |
|2 |Write |
|3 |Change|
+-----------+------+

EmployeeRight
+-----------+--------+
|EmplyeeID | RightID|
+-----------+--------+
|1 |1 |
|1 |2 |
|2 |1 |
+-----------+--------+


Now I need to give all users the right "Change" which have the right "Write". But users that already have that right should not get it twice.

My current attempt does not work:

INSERT INTO EmployeeRight
(SELECT '3', EmployeeID FROM EmployeeRight WHERE RightID = 2 AND RightID !=3)

Answer

Within a single row RightID can't be both 2 and <>2, you need a query like this:

INSERT INTO EmployeeRight 
SELECT EmployeeID, 3 
FROM EmployeeRight 
WHERE RightID = 2 -- existing right 2
  AND EmployeeID NOT IN
   (
     SELECT EmployeeID 
     FROM EmployeeRight 
     WHERE RightID = 3 -- but not those who already have right 3
   )