CODEWITHSUNDEEP
sqloracle-database
Thej
Thej

Reputation: 375

How to compare multiple column with same values (Oracle SQL)

I need to compare multiple values of a table with same set of values in single shot without using multiple AND condition in WHERE clause. Please let me know is any methods to do it.

code:

select count(1)
  from test
 where pre_sys_id1 in (select sys_id from test where auto_id = 10)
   and pre_sys_id2 in (select sys_id from test where auto_id = 10)
   and pre_sys_id3 in (select sys_id from test where auto_id = 10)
   and pre_sys_id4 in (select sys_id from test where auto_id = 10)
;

Upvotes: 0

Views: 5210

Answers (2)

guthy
guthy

Reputation: 326

You can try: 

select count(1)
  from test
 where (pre_sys_id1, pre_sys_id2, pre_sys_id3, pre_sys_id4)
   in (select sys_id, sys_id, sys_id, sys_id from test where auto_id = 10)

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Select count(1)
from test
where  (select sys_id from test where auto_id=10) = all (pre_sys_id1,pre_sys_id2,pre_sys_id3,pre_sys_id4)

Or join 

Select count(1)
from test t1
join test t2 on( sys_id= pre_sys_id1 and sys_id= pre_sys_id2 and sys_id= pre_sys_id3  and sys_id= pre_sys_id4) 
where t2.auto_id = 10 ;

Upvotes: 1

Related Questions