Hi I have hierarchical based question.

I have two tables.

Temp table:

Main Table:

• Higher rank from temp table(ex. 2) can approve same or lower rate (ex. 2 & 3) records in main table.
• So if lower rank's role from temp table (ex. 3) didn't approved a higher rate's record in main table it will not an exception(i.e. 0).
• But in main table for a ID which belongs to high rate (ex. 2) record, if lower rank's (ex. 3) role from temp table has approved the record but there is no same rank(as records's rate) (i.e. 2) or higher rank (i.e 1) as per the record's rate have approved any of the ID's record then it will be considered as exception (i.e. 1).
• for a record (ex. rate = 2) if same or higher rank (i.e. 1 or 2) didn't approved then only that particular record should be exception (1).

All those conditions wrt temp table need to be applied in main table as per rate of the record, role and approved status grouping by ID.

The output should look like below. Anyone know how can i achieve this.

Output:

CodePudding user response：

I suggest to create a new fuction that can apply the proper logic and return the result as a 0/1 Flag.

CodePudding user response：

If I have well interpreter the issue:

1. create function

   CREATE OR REPLACE FUNCTION `get_flag`(v_ID int, v_Rate int, v_Rank int, v_Approved varchar(1)) RETURNS int(11)
   BEGIN
    DECLARE TEST INT DEFAULT 0;
    -- Higher rank from temp table(ex. 2) can approve same or lower rate (ex. 2 & 3) records in main table.
    IF v_Approved = 'Y' AND v_Rank > v_Rate THEN
   
   -- But in main table for a ID which belongs to high rate (ex. 2) record, if lower rank's (ex. 3) role from temp table has approved the record but there is no same rank(as records's rate) (i.e. 2) or higher rank (i.e 1) as per the record's rate have approved any of the ID's record then it will be considered as exception (i.e. 1).
         SELECT count(*)
           INTO TEST
           FROM main WHERE id = v_ID AND approved = 'Y' AND rate >= v_Rank;
           IF TEST > 0 THEN
              RETURN  0;
         END IF;
           RETURN 1; 
    END IF;
   
   
    -- for a record (ex. rate = 2) if same or higher rank (i.e. 1 or 2) didn't approved then only that particular record should be exception (1).
    IF v_Approved = 'N' AND v_Rank <= v_Rate THEN
        RETURN  1;
    END IF;
   
   
    -- So if lower rank's role from temp table (ex. 3) didn't approved a higher rate's record in main table it will not an exception(i.e. 0). 
    RETURN 0;
   END
   

2. use it as follows

   SELECT m.ID, m.Rate, m.Role, m.Approved, get_flag(m.ID, m.Rate, t.Rank, m.Approved) flag
   FROM main m
   JOIN temp t ON (t.Role = m.Role);
   

if needs change the functions as you prefer.

Bye