Query that Matches One or More Values from Previous Query

[PaulPA]

I'm trying to run a query that depends on results from another query and I can't seem to get this to work.

First, I want a query that will result in one or more values being returned:

$query = "SELECT variable1 FROM table1 WHERE variable2=something";

The result will probably lead to one or more results for variable1. The next query requires a match to at least one of the values in variable1.

$query = "SELECT variable3 FROM table2 WHERE variable4=one_of_the_values_in_variable1";

I've gotten this to work with a foreach loop but the problem with this is that it looks at all variable1 values separately instead of all at once. That is, I want the the second query to return a result as soon as it finds one that meets a variable1 value NOT by looking first for variable1-value1 matches, then variable1-value2 matches, etc.

[darrenG]

If you want MySQL to stop looking after it finds one hit, you could try something like:

select var from table where field = var or field = var2..... limit 1

Hope that helps

[Demaestro]

You want to pass your second query an array.... So the second query should look like this

***********************
$query = "SELECT variable3 FROM table2 WHERE variable4 in list_of_values_from_other_query";
***********************

All i changed was the "=" to "in".... SQL will error though if the variable "list_of_values_from_other_query" isn't of type array so make sure that it is. You can do that by assigning the results of the first query to an array.

So let's say your first query returns ids..... you want to loop through the results and write the ids to an array.

like this......(sudo code **** this is not in PHP you will have to convert sorry)

list_of_values_from_other_query = []
For record in results_from_query_one:
..list_of_values_from_other_query.append(record['id'])

Then pass the second query the array.

Hope this makes sense and if is what you are needing.... if not post back and i will take another stab at it with you.

[edited by: Demaestro at 5:38 pm (utc) on July 11, 2007]

[PaulPA]

Thanks for your help. I decided to go a different route with this and created a PHP script to create the WHERE statement. Looks something like this:

$query = "SELECT variable1 FROM table1 WHERE variable2=something";

$where_var = "";
foreach($rows as $row) {
$where_var .= " variable_id=".$row->variable1." OR";
}
$where_var = trim($where_var, "OR" );

$query = "SELECT variable3 FROM table2 WHERE $where_var";

The effect of this is that the WHERE statement in the second query will be something like if two results are found:

WHERE variable_id=variable1-A OR variable_id=variable-B

Note I had to trim off last "OR" from the $where_var variable.

I'm sure there is a better way and maybe that way is using an array.

[borntobeweb]

You can also use a temporary table to store the result from the first query:

$query0 = "create temporary table temp1 select variable1 FROM table1 WHERE variable2=something";

$query1 = "SELECT * FROM temp1";

$query2 = "SELECT table.variable3 FROM table2, temp1 WHERE table2.variable4 = temp1.variable1";

or better yet:

$query2 = "SELECT variable3 FROM table2 WHERE EXISTS (select * from temp1 where table2.variable4 = temp1.variable1)";

Of course if you don't really need to do anything else with the results of the first query, you just do:

SELECT variable3 FROM table2 WHERE EXISTS (select * from table1 where table2.variable4 = table1.variable1 and table1.variable2 = something)

[I haven't tried to run any of the above queries so there may be syntax errors, but i hope you get the idea.]

[LBmtb]

SELECT variable3
FROM table2
WHERE variable4 IN (SELECT variable1 FROM table1 WHERE variable2=something)
LIMIT 1

You're welcome. :)

[edited by: LBmtb at 4:37 am (utc) on July 12, 2007]

[PaulPA]

Obviously more than one way to get at this. Thanks all!