CODEWITHSUNDEEP
sqloracle-database
Ragav
Ragav

Reputation: 229

Using OR Clause in Filter Condition - Oracle

I have a below requirement in which the SQL will have to filter records based on few OR conditions.

Table 1
**********
S   A       B
1   Crow    Bird 
2   Apple   Fruit  
3   Orange  Fruit
4   Fox     Animal 
4   Dog     Animal 
4   Cat     Animal 
5   Apple   Mobile 
6   Apple   Product

Table 2
*********
S   A       B
1   Crow    Bird

Join Table 1 Left Join Table2 on Column S,A,B

And pull records after applying these filters

  1. Table2.A is NULL
  2. ((Table1.B='FRUIT' or Table1.B='Mobile') and (Table1.A<>'Apple'))
  3. ((Table1.B='Animal') and ( Table1.A='Fox' or Table1.A='Dog'))

So the final Output looks like

S   A   B
1   Crow    Bird - Filtered Out by Condition 1 
2   Apple   Fruit  -Filtered Out by Condition2
3   Orange  Fruit
4   Fox     Animal 
4   Dog     Animal 
4   Cat     Animal - Filtered out by Condition 3
5   Apple   Mobile - Filtered Out by Condition2
6   Apple   Product

I tried below query but it returned incorrect result. 

Select T1.S, T1.A, T1.B 
from (Select * from Table1 ) T1 
left join ( Select * from Table 2 ) T2 
on T1.S=T2.S and T1.A = T2.A and T1.B = T2.B 
where ( ( T2.A is NULL) or ((T1.B='FRUIT' or T1.B='Mobile') 
  and (T1.A<>'Apple')) or ( ((T1.B='Animal') 
  and ( T1.A='Fox' or T1.A='Dog')) ) )

Upvotes: 0

Views: 737

Answers (1)

krokodilko
krokodilko

Reputation: 36087

To diagnose the source of the problem, run this query:

Select T1.S, T1.A, T1.B, T2.A as T2_A,
      CASE WHEN T2.A is NULL 
           THEN 'TRUE'
           ELSE 'FALSE'
      END filter1,
      CASE WHEN (T1.B='Fruit' or T1.B='Mobile') AND (T1.A<>'Apple')
           THEN 'TRUE'
           ELSE 'FALSE'
      END filter2,
      CASE WHEN ((T1.B='Animal') and ( T1.A='Fox' or T1.A='Dog'))
           THEN 'TRUE'
           ELSE 'FALSE'
      END filter3  
from (Select * from Table1 ) T1 
left join ( Select * from Table2 ) T2 
on ( T1.S=T2.S and T1.A = T2.A and T1.B = T2.B )
order by t1.s
;

The query display a result of join, evaluates a value of each filter condition and displays true/false values for each row and each filter.
See this demo: http://www.sqlfiddle.com/#!4/16c20/20

I think your query works fine, it gives you desider results exept the first row:

1   Crow    Bird - Filtered Out by Condition 1

however this row doesn't meet any condition you have shown.

Upvotes: 1

Related Questions