# Issue

I need some help on figuring the solution to this in Oracle SQL.

I have the 2 tables below the Oppty Table and the Acc Table

Acc_ID | Oppty ID | Product1 |
---|---|---|

123 | JJJ | apples |

123 | ZZZ | oranges |

567 | aaj | apples |

888 | UUU | berries |

Acc_ID | Product2 |
---|---|

123 | apples |

123 | apples |

123 | oranges |

567 | bananas |

567 | grapes |

567 | apples |

888 | oranges |

And then I do a LEFT JOIN on Oppty.ACC_ID = Acc.ACC_ID, so I get the following table with an added calculated field (CF) if product1 = product2 then TRUE else FALSE

Acc_ID | Oppty ID | Product1 | Product2 | CF |
---|---|---|---|---|

123 | JJJ | apples | apples | TRUE |

123 | JJJ | apples | apples | TRUE |

123 | JJJ | apples | oranges | FALSE |

123 | zzz | oranges | apples | FALSE |

123 | zzz | oranges | apples | FALSE |

123 | zzz | oranges | oranges | TRUE |

567 | aaj | apples | bananas | FALSE |

567 | aaj | apples | grapes | FALSE |

567 | aaj | apples | apples | TRUE |

888 | UUU | berries | oranges | FALSE |

Right now I am comparing line by line where Product1t != Product2 is FALSE, however I want the results to look at each Acc_ID to see if there a Product Match and if so then TRUE, basically I am trying to do an IN statement dynamically?

For example in line 3 where Product1 is apples and Acc_ID is 123, it is currently showing FALSE because apples != oranges, however, I want it to look at all Product2 under the same Acc_ID (123) and spit out TRUE if there's a match - so since there are other apples under Product2 for Acc_ID 123 it would spit out TRUE.

The new table should look like this, thank you for your help!

Acc_ID | Oppty ID | Product1 | Product2 | CF |
---|---|---|---|---|

123 | JJJ | apples | apples | TRUE |

123 | JJJ | apples | apples | TRUE |

123 | JJJ | apples | oranges | TRUE |

123 | zzz | oranges | apples | TRUE |

123 | zzz | oranges | apples | TRUE |

123 | zzz | oranges | oranges | TRUE |

567 | aaj | apples | bananas | TRUE |

567 | aaj | apples | grapes | TRUE |

567 | aaj | apples | apples | TRUE |

888 | UUU | berries | oranges | FALSE |

# Solution

You can also use below solution for your purpose.

```
select
op.Acc_ID
, op.Oppty_ID
, op.Product1
, ac.Product2
, CASE WHEN EXISTS (
SELECT 1
FROM Acc ac2
WHERE ac2.Acc_ID = op.Acc_ID
AND ac2.Product2 = op.Product1
)
THEN 'TRUE'
ELSE 'FALSE'
END cf
from Oppty op
left join Acc ac
on op.Acc_ID = ac.Acc_ID
ORDER BY op.Acc_ID, op.Product1
;
```

Answered By - Mahamoutou Answer Checked By - David Marino (PHPFixing Volunteer)

## 0 Comments:

## Post a Comment

Note: Only a member of this blog may post a comment.