Set MYSQL WHERE condition from previous SELECT

user_637130 0
Viewed 51

I have the following MySQL query

SELECT `category` 
FROM `jeopardy_questions` 
WHERE `amount` = "$2,000" 
GROUP BY `category` 
HAVING COUNT(*) > 4 
ORDER BY RAND() LIMIT 1

This will grab me a random category where there is at least 5 questions in that category.

Now I want to grab all the rows for that category. So how can I do a second SELECT WHERE category is equal to the category returned from the previous query?

I tried the following but I believe the RAND() is causing it to crash/timeout.

SELECT *
FROM `jeopardy_questions`
WHERE `category` = (
    SELECT `category` 
    FROM `jeopardy_questions` 
    WHERE `amount` = "$2,000" 
    GROUP BY `category` 
    HAVING COUNT(*) > 4 
    ORDER BY RAND() LIMIT 1
)
mysql
1 Answers

You can use the above query as a subquery. Something like this:

SELECT *
FROM `jeopardy_questions`
WHERE `category` = (
    SELECT `category` 
    FROM `jeopardy_questions` 
    WHERE `amount` = "$2,000" 
    GROUP BY `category` 
    HAVING COUNT(*) > 4 
    ORDER BY RAND() LIMIT 1
)
user_136012210
Related