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

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

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 | .