Jérôme Deguine Jérôme Deguine - 5 months ago 20
SQL Question

Google Sheet query where B = X only if B is not empty

I would like to make a query to filter some information in my table depending on different fields I can choose from a drop down. (D1, D2, D3, D4).

This query below works, but only if I specify 4 criteria.
I would like to have a query only on D1 and D2 criteria if I leave D3 and D4 empty.

=QUERY(Data!A2:S;
"select *
where B = '"&D1&"' >> only if D1 not empty
and C = '"&D2&"' >> only if D2 not empty
and G = '"&D3&"' >> only if D3 not empty
and I = '"&D4&"' >> only if D4 not empty
" )

Answer

To ignore a condition when the relevant input cell is empty, use a conditional statement that will replace its content by the column letter in comparison:

= QUERY(Data!A2:S;
  "select * 
  where B = "  & if(len(D1), "'"&D1&"'", "B") 
  & " and C = " & if(len(D2), "'"&D2&"'", "C")  
  & " and G = " & if(len(D3), "'"&D3&"'", "G")  
  & " and I = " & if(len(D4), "'"&D4&"'", "I")  
  )

This way, if D1 is empty, the comparison becomes "... where B = B and ..."