CODEWITHSUNDEEP
mysqlsqlnode.jsperformance
manoj
manoj

Reputation: 47

Mysql If else in Where clause

SELECT * FROM table WHERE field1 ="abc" AND field2="xyz"

if this query returns me 0 rows then i have to reduce my filter criteria in where clause like SELECT * FROM table WHERE field1 ="abc" (need to remove one field from Where clause)

this can i handle with case when in where clause i.e

SELECT * 
  FROM table 
 WHERE CASE WHEN((SELECT COUNT(*) FROM table WHERE field1 ="abc" AND field2="xyz" )>0)
THEN field1 ="abc" AND field2="xyz"
ELSE field1 ="abc" END

BUT I WANT THIS TO DONE WITH IF ELSE STATEMENT(or any other better option instead of case statement)

Upvotes: 0

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you are expecting exactly one row, then a simple method is:

SELECT t.*
FROM table t
WHERE t.field1 = 'abc' 
ORDER BY ( field2 = 'xyz' ) DESC
LIMIT 1;

If more than one row is possible, then:

SELECT t.*
FROM table t
WHERE field1 = 'abc' AND
      (field2 = 'xyz' OR
       NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.field1 = t.field1 AND t2.field2 = 'xyz')
      );

Upvotes: 2

Related Questions