Nesting results of one query in another

[havencruise]

Hi all,

I'm new to php and I was trying to send the results of one query to another query. I need the results of both queries.

Here are the queries

$cui1 = mysql_query("SELECT DISTINCT CUI FROM MRSTY WHERE STY='ABC' ");

$cui2 = mysql_query("SELECT DISTINCT CUI FROM MRSTY WHERE STY='XYZ' ");

 

I want to know if this is possible

$cuis = mysql_query("SELECT CUI1,CUI2,REL FROM MRREL WHERE CUI1 IN $cui1 OR CUI1 IN $cui2 ");

 

Thanks.

[revraz]

Give it a try and see.

[havencruise]

I'm sorry, it doesn't work. Is there anything which accomplishes it in the same way without having to send the query in again?

[revraz]

Explain what "it doesn't work" mean.

 

What does $cui1 and $cui2 contain after the first 2 queries are run?

[havencruise]

The first two queries get a list of all CUIs - which are basically IDs of a certain string STY like xyz or abc. CUI is not the primary key in the MRSTY table. I need the results of this table.

The third query is an "IN" subquery which basically sends the CUIs obtained in the first two queries as conditions. It is more like saying:

Select name from employee where cafeteria_id in 
   (select cafeteria_id from cafe where branch = "new york") 
or cafeteria_id in (select cafeteria_id from cafe where branch = "london")

What I'm trying to do is perform the first two queries and store the results in a variable like:

$cui1 = select cafeteria_id from cafe where branch = "london"
$cui2= select cafeteria_id from cafe where branch = "new york"

and then send the first two variables into the third query:

Select name from employee where cafeteria_id in $cui1 or cafeteria_id in $cui2

[Psycho]

You only need one query

 

SELECT CUI1, CUI2, REL

FROM MRREL

WHERE CUI1 IN (SELECT DISTINCT CUI
               FROM MRSTY
               WHERE STY='ABC' OR STY='XYZ')

[havencruise]

Thank you, I already have the CUIs in variables. My question is how do I send these values from the variables to the query.

 

The code below does not work. What can I do to make it work?

$cuis = mysql_query("SELECT CUI1,CUI2,REL FROM MRREL WHERE CUI1 IN $cui1 OR CUI1 IN $cui2 ");

[Psycho]

Thank you, I already have the CUIs in variables. My question is how do I send these values from the variables to the query.

 

You don't. There is no reason to run multiple queries. And you don't have the CUI's in a variable.

 

$cuis = mysql_query("SELECT CUI1,CUI2,REL FROM MRREL WHERE CUI1 IN $cui1 OR CUI1 IN $cui2 ");

In that code $cuis is a resource identifier to a MySQL result set. You would have to loop through that result set to get the values to use in a subsequent query. Why would you not want to run just one queery anyway?

[havencruise]

You don't. There is no reason to run multiple queries. And you don't have the CUI's in a variable.

In that code $cuis is a resource identifier to a MySQL result set. You would have to loop through that result set to get the values to use in a subsequent query.

Thank you.. That sounds exactly like what I need to learn to do.

Why would you not want to run just one queery anyway?

 

The first two queries which output into $cui1 and $cui2 take about an hour to select about 70 values. I do not want to execute that in another nested query. I would rather pass the result set as a parameter to the third query.