How to add a row to a table based on condition in SQL server-CodePudding

I have a table in SQL server as follows:

each REQUEST_ID must have a user's with the ROLE: MANAGER, LEADER, EMLOYEE.

ROLE MANAGER, LEADER can occur only once,

ROLE EMLOYEE can occur multiple times.

for some cases, the user with "ROLE MANAGER" is the same for "ROLE LEADER", but the system insert only one record by user like the REQUEST "N-C-3" in orange.

I want to insert a new row with the same information of the "ROLE MANAGER" for the "ROLE LEADER" for each REQUEST without a "ROLE LEADER".

The desired result is :

CodePudding user response：

First, You should select the roles by grouping request_id column just for manager role, then check the distinct roles count. If the count lower than 3, it means you should insert user_id for those request_id's.

I try to explain the procces which you try , because no one can give you correct user id which you would use in your insert statement.

CodePudding user response：

If you can select rows, then you can easily turn that into an INSERT statement. So let's focus on selecting the rows. Start with:

select * from dbo.table where ROLE = 'MANAGER';

That will return one row per REQUEST_ID (according to your assumption) but it is not correct. We need to filter out rows where another exists (hint) with the REQUEST_ID and role of LEADER.

select * 
  from dbo.table as src 
 where ROLE = 'MANAGER'
   and not exists (select * from dbo.table as srclead 
        where srclead.REQUEST_ID = src.REQUEST_ID 
          and srclead.ROLE = 'LEADER');

Note that there are other ways to accomplish the same goal. Confirm that works and then convert it to an INSERT statement - which I leave to you as a learning exercise.