0

I'm pretty new to MySql and this might seem like a stupid question, but i can't seem to find the answer anywhere.
I have two tables that I'm working with. The first table contains a lot of columns filled with Integer values.
such as: No_of_Landlines No_of_mobiles
The second table is a Rule Table which contains a rule name followed by the actual rule.
Eg: 

 ID    RuleName    Rule
  1    Example     No_of_Landlines > No_of_Mobiles

The query that i want to run is this:

SELECT * 
FROM DemoTable 
WHERE (SELECT Rule from RuleTable WHERE ID=1)

What i want it to do is this: 

SELECT * 
FROM DemoTable 
WHERE No_of_Landlines > No_of_Mobiles

When i run the first query against my database, it returns empty because the value of the rule being fetched from the Rule Table is a string and not a column name that I want, unlike query two under it.
How do I make the values of the string being returned into column names and execute the entire query?

One of my friends who's good with MSSQL suggested this: 

DECLARE @a varchar(4000)
SET @a=(SELECT Rule FROM RuleDB WHERE ID=1)
print @a

EXEC('SELECT * FROM DemoTable WHERE ' + @a)

I tried this in MSSQL and it does work the way I want it to.
Is there a way to achieve this in MySql?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
BoreBoar
  • 2,619
  • 4
  • 24
  • 39
  • 1
    Take a look at this SO post on how to use dynamic SQL in MySQL http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure – WorkSmarter Feb 19 '15 at 18:11

2 Answers2

1

You can use EXECUTE command, this is a link to the syntax! .

Here is an example script:

SELECT @Q:= CONCAT('SELECT * FROM DemoTable WHERE ', Rules.Rule) FROM Rules     WHERE ID=3;
PREPARE stq FROM @Q;
EXECUTE stq;
DEALLOCATE PREPARE stq;

In this script you create que query you want to execute in the variable @Q concatenating part of the query with the result of the rule you are fetching from the Rules table.

Then you prepare the statment, execute and deallocate.

Jose Marfil
  • 645
  • 8
  • 14
0

You can define variables in MySQL and use them the same way you used them in MSSQL.

The complete documentation can be found here: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

payamsabz
  • 26
  • 2