Topic
  • No replies
rk128
rk128
9 Posts

Pinned topic Issue using like with % on null values

‏2009-12-21T12:33:22Z |
I am adding a filter on several columns in the view and form builder with the Input Page Options. For the most part it works as expected however I am having an issue with one field which is included in the query by the use of LEFT OUTER JOIN.

If the field value is null the % filter does not include those rows in the resultset. For example, the following statement will include rows where field3 is null.

SELECT distinct table1.field1, table1.field2, table2.field3
FROM table1
LEFT OUTER JOIN table2 ON table1.id = table2.id

As soon as the “like ‘%’” is added the rows where field3 is null are not included in the resultset. I can include “or field3 not null” to have the null value rows in the resultset but that is not easy to handle with the filters in the view and form builder.

SELECT distinct table1.field1, table1.field2, table2.field3
FROM table1
LEFT OUTER JOIN table2 ON table1.id = table2.id
WHERE table2.field3 like '%'

I need to include the rows with null values in the resultset. Thanks for any advice.