mysqldatabasevariablesuser-variables

Querying a table for an uservariable list

I have the following Problem: I have a table table1 with multiple columns col1 through col2:

+------+------+  
| col1 | col2 |    
+------+------+  
|  1   |  4   |  
|  2   |  4   |  
|  3   |  4   |  
|  4   |  5   |  
+------+------+

Now I need all entries col1-entries with a four in in col2, for which I use: 

SELECT @col2fours := col1 FROM table1 WHERE col2 = 4;

Now I have a table2 where I want to set the rows where col3 has one of the values stored in @col2fours. For this I use:

SELECT * ID FROM table2 WHERE col3 IN @col2fours;

This doesn't work. I already tried = and having instead of in as well as using Group_concat. Nothing worked for me so far.
I would like to do this directly in mySQL because the database lies on a server and I want to reduce accesses to the database as much as possible. The real problem has somewhat more tables involved than 2 ;). Appreciate any input, thanks.

Solution

  • you can use sub query like this directly 

    SELECT * FROM table2 WHERE col3 IN (SELECT col1 FROM table1 WHERE col2 = 4);