CODEWITHSUNDEEP
mysqlvariables
Mathijs Segers
Mathijs Segers

Reputation: 6238

Store MYSQL result and use as IN statment in different query

I was wondering if someone could point out how to achieve this.

I have a pretty heavy query (mostly because of the amount of records involved 10 years of history with purchases which is thousands every day).

Anyhow simplified It would be something like this (more joins involved)

SELECT customerNumber from purchaseOrder where somestatus = someStatus;

In this case there are multiple joins but what I want to know are the customer numbers, they're not connected through relations since orders can also be bought by non registered people as a one-off thing.

Now this query would result in about 950 records for now. It takes up to 30 seconds to load (which is fine).

However Now I Want to re use the results in a different query e.g.:

SELECT * from registeredUsers wherestatus 
IN(Some values which mean the account is not active)
and customerNumber in (results from query 1).

So this second query is pretty fast when I paste the numbers in the IN part. But I would like to get this to work dynamically, since I need to create some variants of query 2 for different cases.

Is this an option?

I tried putting the results in a variable, but then I get the "Subquery returns multiple rows" error, which makes sense.

So any other ways to do this? I was thinking of creating a temporary table, that might work?

Upvotes: 2

Views: 65

Answers (1)

imran arshad
imran arshad

Reputation: 274

Yes temporary table would be the way to go here. Store the results of query 1 in a temporary table and in query 2 use select * from the temporary table in the subquery.

CREATE temporary table temp (index (customerNumber))
    SELECT customerNumber from purchaseOrder where somestatus = someStatus;

SELECT * from registeredUsers wherestatus 
    IN(Some values which mean the account is not active)
    and customerNumber in (SELECT * from temp);

You can skip the index from the temporary table if your registeredUsers table does not have index on customerNumber

Upvotes: 1

Related Questions