how to compare multiple columns with same value in Oracle SQL with OR

Question

I have the below query where i am comparing multiple columns with same sub query multiple times. is there a way to call only one sub query compare with multiple columns. I know it is possible for AND but is it possible for OR.

select * from catalog 
where 
APPROVER_USER=(select usr_key from usr where usr_login='abcd')
or CERTIFIER_USER =(select usr_key from usr where usr_login='abcd')
or FULFILLMENT_USER =(select usr_key from usr where usr_login='abcd')
;

Sorry for the wrong query, updated the sub query. is it make sense now ? — ashok_p
– ashok_p, Commented Apr 26, 2020 at 18:20

Gordon Linoff · Accepted Answer · 2020-04-26 18:25:54Z

1

You seem to be comparing user_key in the subquery based on a specific user. There seems to be no need for the usr table at all:

select c.*
from catalog c
where 437391 in (c.APPROVER_USER, c.CERTIFIER_USER, c.FULFILLMENT_USER);

EDIT:

For the revised question, I would just recommend join:

select c.*
from catalog c join
     users u
     on u.usr_login = ? and
        u.user_key in (c.APPROVER_USER, c.CERTIFIER_USER, c.FULFILLMENT_USER);

edited Apr 26, 2020 at 18:25

answered Apr 26, 2020 at 18:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to compare multiple columns with same value in Oracle SQL with OR

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related