If else on WHERE clause

Question

I ve this query   SELECT   id      naam   FROM   klanten   WHERE    email  LIKE     domain nl   OR   email2  LIKE     domain nl       But I want to do something like this   SELECT   id      naam   FROM   klanten   WHERE IF  email   gt  0   email  LIKE     domain nl       email2  LIKE     domain nl       How to check if email exist  I want to use email and if this field is empty I want to use email2  How do I accomplish this

User · Answer

You want to use coalesce     where coalesce email  email2  like   anja fiskkoer nl     If you want to handle empty strings      versus NULL  a case works   where  case when email is NULL or email      then email2 else email end  like   anja fiskkoer nl     And  if you are worried about the string really being just spaces   where  case when email is NULL or ltrim email       then email2 else email end  like   anja fiskkoer nl     As an aside  the sample if statement is really saying  If email starts with a number larger than 0    This is because the comparison is to 0  a number   MySQL implicitly tries to convert the string to a number   So   abcd de com  would fail  because the string would convert as 0   As would  0abc de com    But   1abc de com  and  01abc de com  would succeed

User · Answer

Here is a sample query for a table having a foreign key relationship to the same table with a query parameter     SET  x   -1  SELECT id  categoryName  FROM Catergory WHERE IF  x  gt  0 category ParentId    x category ParentId IS NOT NULL      x can be changed

User · Answer

Note the following is functionally different to Gordon Linoff s answer  His answer assumes that you want to use email2 if email is NULL  Mine assumes you  want to use email2 if email is an empty-string  The correct answer will depend on your database  or you could perform a NULL check and an empty-string check - it all depends on what is appropriate for your database design    SELECT   id      naam   FROM   klanten   WHERE  email  LIKE    anja fiskkoer nl   OR  LENGTH email    0 AND  email2  LIKE    anja fiskkoer nl

User · Answer

IF is used to select the field  then the LIKE clause is placed after it   SELECT   id      naam   FROM   klanten   WHERE IF  email          email    email2   LIKE     domain nl

User · Answer

try this  hope it helps   select user display image as user image  user display name as user name  invitee phone     CASE      WHEN invitee status 1 THEN  attending       WHEN invitee status 2 THEN  unsure       WHEN invitee status 3 THEN  declined       WHEN invitee status 0 THEN  notreviwed  END   AS  invitee status  FROM your tbl

[mysql] If else on WHERE clause

Examples related to mysql

Examples related to sql

Examples related to if-statement