How to find IN list vs compare line by line Join ORACLE SQL-CodePudding

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

CodePudding user response：

select  "Acc_ID"    
       ,"Oppty ID"  
       ,"Product1"
       ,"Product2"
       ,case count(case "Product1" when "Product2" then 1 end) over(partition by "Acc_ID") when 0 then 'False' else 'True' end  as CF
from Oppty o left join Acc a using("Acc_ID")

Acc_ID	Oppty ID	Product1	Product2	CF
123	JJJ	apples	apples	True
123	ZZZ	oranges	apples	True
123	JJJ	apples	apples	True
123	ZZZ	oranges	apples	True
123	JJJ	apples	oranges	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

Fiddle