How can I use an SQL query's result for the WHERE clause of another query?

Question

Okay, basically I have a table that contains statements like:

incident.client_category = 1
incident.client_category = 8
incident.severity = 1
etc.

I would like to use the contents from this table to generate other tables that fulfill the conditions expressed in this one. So I would need to make it something like

SELECT * FROM incident WHERE incident.client_category = 1

But the last part of the where has to come from the first table. Right now what I'm trying to do is something like

SELECT * FROM incident WHERE (SELECT condition FROM condition WHERE id = 1)

id = 1 stands for the condition's id. Right now I only want to work with ONE condition for testing purposes. Is there a way to achieve this? Because if there isn't, I might have to just parse the first query's results through PHP into my incident query.

Table schemas:

Answer 1

Engineering Suggestion - Normalize the DB

Storing a WHERE clause, like id = 10, in a field in a MySQL table, is not a good idea. I recommend taking a look at MySQL Normalization. You shouldn't store id = 10 as a varchar, but rather, you should store something like OtherTableid. This allows you to use indices, to optimize your DB, and to get a ton of other features that you are deprived of by using fields as WHERE clauses.

But sometimes we need a solution asap, and we can't re-engineer everything! So let's take a look at making one...

Solution

Here is a solution that will work even on very old, v. 5.0 versions of MySQL. Set the variable using SET, prepare a statement using PREPARE, and execute it using EXECUTE. Let's set our query into a variable...

SET @query = CONCAT(
     "SELECT * FROM incident WHERE ",
     (SELECT condition FROM condition WHERE id = 1)
);

I know for a fact that this should work, because the following definitely works for me on my system (which doesn't require building any new tables or schema changes)...

SET @query = CONCAT("SELECT id FROM myTable WHERE id = ", (SELECT MAX(id) FROM myTable));

If I SELECT @query;, I get: SELECT id FROM myTable WHERE id = 1737901. Now, all we need to do is run this query!

PREPARE stmt1 FROM @query; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

Here we use a prepare to build the query, execute to execute it, and deallocate to be ready for the next prepared statement. On my own example above, which can be tested by anyone without DB schema changes, I got good, positive results: EXECUTE stmt1; gives me...

| id | 1737901 | .

Answer 2

here is one way to achieve your goal by using what is called dynamic sql, be ware that this works only select from condition table returns only one record.

declare @SQLSTRING varchar(4000)
,  @condition VARCHAR(500) -- change the size to whatever condition column size is

SELECT @condition = condition
FROM
    condition
WHERE
    id = 1
 SET @SQLSTRING= 'SELECT  * FROM incident WHERE  ' + @condition
                
exec sp_executesql(@SQLSTRING)

Answer 3

Since you have also tagged the question with PHP, I would suggest using that. Simply select the string from the condition table and use the result to build up a SQL query (as a string in PHP) including it. Then run the second query. Psudo-code (skipping over what library/framework you re using to call the db):

$query = "select condition from condition where id = :id";
$condition = callDbAndReturnString($query, $id);
$query = "select * from incident where " . $condition;
$result = callDb($query);

However, be very careful. Where and how are you populating the possible values in the condition table? Even how is your user choosing which one to use? You run the risk of opening yourself up to a secondary SQL injection attack if you allow the user to generate values and store them there. Since you are using the value from the condition table as a string, you cannot parametrise the query using it as you (hopefully!) normally would. Depending on the queries you run and the possible values there as conditions, there might also be risk even if you just let them pick from a pre-built list. I would seriously ask myself if this (saving parts of SQL queries as strings in another table) is the best approach. But, if you decide it is, this should work.