Nested loops should return only matched rows-CodePudding

I have a stored procedure that should look for email and name in 2 different tables.I have 2 cursors and i retrieve all email and name for an employee from table a and then loop through the table 2 to find email and name for the same employee in that table. I have another column that should say if the value matches or no.

procedure proc_test IS 
Cursor a is Select 1.id  id,
           2.name name,
           2.email email,
           3.phone phone,
           4.address address from table 1 join table 2 on table 1 where table1.id=table2.id join table 3 on table 1 where table1.id=table3.id join table 4 on table 1 where table1.id=table4.id

Cursor b is  Select 5.id,
       5.name,
       5.email from table 5 where 5.table_id=id;

    BEGIN 
 FOR p IN a LOOP    

id=p.id; name=p.name; email=p.email; phone=p.phone; address=p.address;

 FOR q in C LOOP

`IF q.name=name and q.email=email then
    matched_row:='Y' 
    dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row);` 
else 
 IF q.name=name OR q.email=email then  
    matched_row:='Y' 
    dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row); 
 else 
   matched_row:='N' 
   dbms_output.put_line(id||name||email||phone||address||q.name||q.email||matched_row);
     ENDIF; 
END IF; 

END LOOP; 
END LOOP; 
end proc_test;

The o/p looks like

ID  Phone   ADDRESS TABLE_NAME      TABLE1_EMAIL          TABLE2_NAME       TABLE2_EMAIL               MATCHED_ROW
1   xxx     yyy     BRANDON CAFFEE  [email protected]       Chandra White     [email protected]       Y
1   xxx     yyy     Chandra White   [email protected] BRANDON CAFFEE    [email protected]             N
1   xxx     yyy     BRANDON CAFFEE  [email protected]       BRANDON CAFFEE    [email protected]             N
1   xxx     yyy     Chandra White   [email protected] Chandra White     [email protected]       Y
2   xxx     yyy     Mary Teresa     [email protected]      Mary Teresa       [email protected]            Y
2   xxx     yyy     Mary Teresa     [email protected]                        [email protected]                N

but i would like it to be

  ID    Phone   ADDRESS TABLE_NAME      TABLE1_EMAIL          TABLE2_NAME       TABLE2_EMAIL               MATCHED_ROW
1   xxx     yyy     BRANDON CAFFEE  [email protected]       Chandra White     [email protected]       Y
1   xxx     yyy     Chandra White   [email protected] Chandra White     [email protected]       Y
2   xxx     yyy     Mary Teresa     [email protected]      Mary Teresa       [email protected]            Y
2   xxx     yyy     Mary Teresa     [email protected]                        [email protected]                N

Is there any other way to work this around instead of looping.

TABLE DATA

    TABLE 1
 ID FIRST_NAME  LAST_NAME  EMAIL
 1  BRANDON     CAFFEE     [email protected] 
 1  Chandra     White      [email protected]
 2   Mary       Teresa     [email protected]

TABLE 2

 ID NAME            EMAIL
 1  BRANDON CAFFEE  [email protected]
 1  Chandra White   [email protected]
 2  Mary Teresa     [email protected]
 2                  [email protected]

CodePudding user response：

Please add data samples for both tables, this should work if I understand your requirement correctly

SELECT Distinct
    a.name, 
    a.email, 
    b.name,
    b.email,
    CASE 
        WHEN b.name IS NULL or a.name IS NULL THEN 'N'  
        ELSE 'Y' 
    END AS MATCHED_ROW 
FROM table1 a 
full outer JOIN table2 b 
ON a.name = b.name
AND a.email = b.email;