1

How could I achieve the following in SQL. (I have trouble with the checking if col1 is empty, check if col2 is "confirmed")

select data from table1 where col1 = "true" or (if col1 is empty, check col2 = "confirmed").

(the value to check for col1 and col2 are different)

jmmom
  • 313
  • 3
  • 7

1 Answers1

3

Assuming col2 is non-null. If it can be null, expand the conditions (similar to the one shown)

where (col1 is not null and col1='true')
or (col1 is null and col2='confirmed')
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Thanks for the answer. However, I tried it with sqlite, It doesn't work. It only take the first condition. the output is only all the data with col1 not null and equal to col1='true'. – jmmom Jan 12 '18 at 08:47
  • My full statement is as below: select name, date from table1 where (col1 is not null and lower(col1) = 'true' ) OR (col1is null and lower(col2) = 'confirmed') ; – jmmom Jan 12 '18 at 08:51
  • I just found out from SQL doc. ............The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =. – jmmom Jan 12 '18 at 10:57
  • Any other option!! – jmmom Jan 12 '18 at 10:58