How to set WHERE conditions if the IF() function returns FALSE and END query if it returns TRUE in mysql

if else condition in mysql query in php
mysql if() function
mysql if multiple conditions
mysql case statement multiple conditions
if else in mysql stored procedure
mysql if not null
sum(case when mysql)
mysql order by case

I want to use mysql to check if a value exists in MySQL Database. If the value exists, I want to do nothing (I don't want to fetch any data). If it does not exist, then I want to set some where conditions.

So this is what I have so far, but its not right. because I still get data fetched, if no where condition is set

SELECT *,
CASE
WHEN ( table_name.record = 'inputrecord')
THEN
    //Do nothing because it is found already
ELSE 
    // since inputrecord does not exist, we will start looking for 'id'
    ( WHERE table_name.id = '123')
END                 
FROM table_name

NOTE: In the above example, I have written WHEN ( table_name.record = 'inputrecord') and not WHEN ( table_name.record != 'inputrecord'). This is because I want to only continue the query if the data is not in the table.

Perhaps its better to use the IF function with the EXISTS function, but I am not sure how to do it.

Any help would be great. Right now I get errors

This is not how SQL works.

SQL always returns a collection of rows. It may be empty, sometimes rows may contain NULLs. You have to work out your conditions to filter the collection.

A generic example: say, we have a table of cars with columns (model, color, year), and you would like to find something about the cars in your table:

(* Find all red cars *)
select * from cars where color = 'red'

(* Find all red cars from 1985 *)
select * from cars where color = 'red' and year = 1985

(* Find all colors which exist both in 1990 and 2000 *)
select distinct color from cars A where year=1990 and exists (select 1 from cars B where year=2000 and B.color = A.color) 

Please tell exactly what you are trying to achieve

EDIT: this should do it

(* select a record cars = 'Audi' only if cars = 'BMW' is not found in the whole table. otherwise. I do not want to select Audi even if it exists *)

select * from cars where model = 'Audi' and not exists (select 1 from cars where model = 'BMW')

here, IF there are BMWs in your table you'll get 0 rows, otherwise a list of Audis

MySQL IF() function, MySQL IF() takes three expressions and if the first expression is true, expr_false, Returns when the condition is FALSE. a string when 1 is less than 3​, so the IF() returns the third expression, i.e. false. IF((SELECT CASE WHEN 1>​0 THEN 'true' ELSE 'false' END),'true','false'); See the following query: IF Function in MySQL Query If function will check one condition and if it is true then the it will return the Next expression ( 2nd one ) and if False then it will return the 3rd expression. Here is the syntax IF(expression_status, expression_if_true, expression_if_false )

 select * 
 from table_name 
 where case when table_name.record <> 'inputrecord' 
               then table_name.id = '123' 
               else 1=1 
       end 

you can apply the given condition in your code

  1. when input condition does not match then apply your filter condition.

  2. when input condition match(else case )then retrieve your desired result.

MySQL IF() Function, The IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE. Syntax. IF(condition, value_if_true, value_if_false). Parameter  Introduction to MySQL IF function. MySQL IF function is one of the MySQL control flow functions that returns a value based on a condition. The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function. The syntax of the MySQL IF function is as follows: IF(expr,if_true_expr,if_false_expr)

Try This...

SELECT *
CASE
 WHEN table_name.record = 'inputrecord' THEN 'Unspecified'
 WHEN table_name.id = '123' THEN 'table by id'
END
FROM table_name;

MySQL: IF-THEN-ELSE Statement, In MySQL, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE. The following is example using the IF-THEN-ELSE statement in a MySQL function: ELSE SET income_level = 'High Income'; END IF; RETURN income_level; END;  Checks all the conditions and if the first condition is met, returns a value and will not read further. If no conditions are fulfilled, then the value will be returned in the ELSE clause. But again, it will return NULL if no ELSE portion and no conditional expressions are true.

MySQL CASE Expressions Explained By Practical Examples, MySQL CASE expression is a control flow structure that allows you to add if-else CASE returns the result in the ELSE clause if the ELSE clause is specified. for equality, you cannot use it with NULL because NULL = NULL returns false. the CASE expression with the SUM() function to calculate the total of sales orders​  IF() function. MySQL IF() takes three expressions and if the first expression is true, not zero and not NULL, it returns the second expression. Otherwise, it returns the third expression. Depending on the context in which it is used, it returns either numeric or string value. Syntax: IF(expression ,expr_true, expr_false); Parameters

MySQL AND Operator By Examples, The AND operator returns false if one of the two expressions evaluate to false. The following table illustrates the results of the AND operator when combining true, false, and null. used in the WHERE clause of the SELECT , UPDATE , DELETE statement to form a condition. This function is called short-circuit evaluation. Let's say I have a boolean method that uses an if statement to check whether the return type should be true or false: And now, I want to use this method as a parameter of an if statement in a different method: So basically what I'm asking is, how do I check what the return type of the boolean method within the parameters of an if statement is?

MySQL 5.7 Reference Manual :: 12.4 Control Flow Functions, The second syntax returns the result for the first condition that is true. If 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; If expr1 is TRUE ( expr1 <> 0 and expr1 <> NULL ), IF() returns expr2 . mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test; mysql> DESCRIBE tmp;  NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. The return value has the same type as the first argument. MySQL evaluates expr1 twice if the arguments are not equal.

Comments
  • would you care to demonstrate please?
  • if your are going to do nothing when WHEN ( table_name.record = 'inputrecord') then why don't you try like "select * from table_name where table_name.record != 'inputrecord' and table_name.id = '123'"
  • @Dr M could I kindly ask you to re-read the question. I have mentioned in my note why I can't do that..
  • to make it simple, I am trying to select a record cars = 'Audi' only if cars = 'BMW' is not found in the whole table's records. otherwise. I do not want to select Audi even if it exists.
  • Thanks a lot for the simple and detailed explanation. Really great thinking :)
  • Thanks let me try this :)
  • Thanks, but what I need is, if the ` 'inputrecord' ` is found even once in the whole table, the condition should return false and 0 rows should return. If however, the ` 'inputrecord' ` is not found at all at any time, in the table, then I'd like to apply the filters and return rows. You're solution is almost there, but it tests the ` 'inputrecord' ` for each row. so if it existed in a previous row, and it does another check for a another row, it will ignore the previous check and return a different result. Any ideas would be great to solve this