I'm trying to write a 2 step process to determine material types for different components.
Step 1: this includes results from a different query containing RecordID and Component Name :
Sample records :
RecordID Component Material
BR39590 00000000000000564792 000000000002073757
BR39590 00000000000000567649 000000000002073757
BR39591 00000000000000567650 000000000002073758
Above RecordId's will contain several component numbers and corresponding Materials
2) Loop through the Material derived from above query result and join to a different table called 'Material' to determine Material type, if Material type belongs to 'A' or 'B' the process should exit out and insert the records into a new table. If Material type does not belong to 'A' or B then Query should go back to step 1 and fetch the next component to look for 'A' or 'B' or blank resultset.
Material Category
000000000002073757 A
000000000002073758 B
Above 2 steps are repeated for all RecordID's
Final Result:
RecordId Material Component Category
BR39590 000000000002073757 00000000000000564792 A
BR39590 000000000002073757 00000000000000567649 A
BR39591 000000000002073758 00000000000000567650 B
