MYSQL : SELECT IN (variable content)?

Question

Is it possible to store store the result of a request in a variable to use it in a "IN ()" ? I want to do something like that :

SET @v1 := (SELECT id FROM table WHERE id > 10);
SELECT * FROM table WHERE id IN (@v1);

That doesn't work because variables can't store multiple rows. So I tried :

SET @v1 := (SELECT GROUP_CONCAT(id) FROM table WHERE id > 10);
SELECT * FROM table WHERE id IN (@v1);

But that only returns me one result (the first), not all the corresponding IDs. Is there a way to do it ?

~MetalFox Dioxymore

Yes, there is, `SELECT * FROM table WHERE id IN (SELECT id FROM table WHERE id > 10)` - or, better way, with a `JOIN` — Alma Do, Sep 28 '15 at 15:21
I think this is a duplicate of http://stackoverflow.com/questions/1524858/create-table-variable-in-mysql. — Greg Viers, Sep 28 '15 at 15:21

score 0 · Answer 1 · answered Sep 28 '15 at 16:06

0

You need subquery,

Try:

SELECT 
    t1.* 
FROM 
    table t1 ,(SELECT id FROM table WHERE id > 10) t2
WHERE t1.id=t2.id

answered Sep 28 '15 at 16:06

Rodney Salcedo

In fact, my problem is a bit more complicated : in my (real) query, I actually use subqueries to join tables with certain conditions. The problem is that the same subquery is used for each join, so the same subquery is called multiple times. That's why I would have wanted a way to only call it once, store the result in a variable and use it in all the joins... – MetalFoxDoS Sep 29 '15 at 08:30
@MetalFoxDoS uhmmmmm, well, why didn't you show the data or data example? I think should be easier to help – Rodney Salcedo Sep 29 '15 at 13:00

1 Answers1