PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Friday, October 28, 2022

[FIXED] How to find IN list vs compare line by line Join ORACLE SQL

 October 28, 2022     left-join, oracle, sql     No comments   

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 
;

demo on db<>fiddle



Answered By - Mahamoutou
Answer Checked By - David Marino (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

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

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing