Dynamically choose which table

Question

I want to have the query dynamically choose which table it looks up against based on a value in a particular row in another table.

I have this query:

SELECT d.name

FROM `database1`.domains AS d
WHERE (SELECT COUNT(u.id) FROM <<d.db_name>>.users u) > 0

I want to use the value of d.db_name as database name. Example: d.db_name = database2

i want this:

SELECT d.name

FROM `database1`.domains AS d
WHERE (SELECT COUNT(u.id) FROM `database2`.users u) > 0

Have a look [here](http://stackoverflow.com/questions/4165020/what-is-dynamic-sql) — JohnHC, May 15 '17 at 14:11
SQL is a special-purpose, declarative language where basic DDL/DML commands like `SELECT` once set are immutable. You can use MySQL stored procs for more dynamic routines, or use application layer, general-purpose languages (PHP, Python, C#, Java, VB) to generate conditional SQL queries on the fly. — Parfait, May 15 '17 at 14:24

score 0 · Answer 1 · answered May 15 '17 at 14:13

You could use variables for this:

SET @table_name = "some_table";

SELECT * FROM @table_name;

If you want to change the variable value depending on the results of your select you coudl use IF like this:

IF(some_column>50, @table_name := "value for true", @table_name := "value for false");

1 Answers1