CODEWITHSUNDEEP
mysql
Adrian
Adrian

Reputation: 87

MySQL condition depending on column value

So, I have more or less this structure of columns in my table:

Name     Age   Operator
----     ---   --------
Jhon      35      >
Michael   30      =
Jess      27      <

Based on that I want to make a query like this

SELECT * FROM mytable WHERE Name = 'John' AND Age > 40

obviosly this will return no results, and thats fine, but my problem is that I want to use Jhon's "Operator" value (> in this case) to make that condition.

Is it possible?

Thank you!

Upvotes: 1

Views: 2559

Answers (2)

fancyPants
fancyPants

Reputation: 51868

You can simply do it like this:

SELECT
*
FROM Table1
WHERE Name = 'Jhon'AND CASE 
WHEN Operator = '>' THEN Age > 10
WHEN Operator = '<' THEN Age < 10
WHEN Operator = '=' THEN Age = 10
END

Upvotes: 3

Raymond Nijland
Raymond Nijland

Reputation: 11602

You also could use MySQL's PREPARE and EXECUTE statements to make dynamic SQL. 

SET @name = 'Jhon';
SET @operator = NULL;
SET @age = 10;

SELECT 
 Operator
INTO 
 @operator
FROM 
 Table1
WHERE
 Name = @name;

SET @SQL = CONCAT(
   "SELECT"
 , " * "
 , " FROM " 
 , " Table1 "
 , " WHERE "
 , "  name = '", @name, "' AND age ", @operator, ' ' , @age
);

SELECT @SQL; # not needed but you can see the generated SQL code which will be executed

PREPARE s FROM @SQL;
EXECUTE s;

see demo https://www.db-fiddle.com/f/3Z59Lxaoy1ZXC4kdNCtpsr/1

Upvotes: 2

Related Questions