Subquery not working

[vchris]

I'm trying to create a subquery but it doesn't seem to be working.$sql1 = "SELECT * FROM db1.table1 WHERE field1=(SELECT post_id FROM db1.table2 WHERE field1=9795)";$result1 = mysql_query($sql1,$dbc1);while($row1 = mysql_fetch_assoc($result1)){ echo $row1['field1'].'<br />';}I get this error:Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in...Both queries work fine by themselves and looks to me like the subquery syntax is fine. Anything I could be missing?

[Ingolme]

Maybe you should do the other query first and add it into the second query after. Like this:$res0 = mysql_query("SELECT post_id FROM db1.table2 WHERE field1=9795",$dbc1);$row0 = mysql_fetch_array($res0);$sql1 = "SELECT * FROM db1.table1 WHERE field1=(".$row0['post_id'].")";$result1 = mysql_query($sql1,$dbc1);while($row1 = mysql_fetch_assoc($result1)){echo $row1['field1'].'<br />';}

[vchris]

But why would I have to do that? That's why they invented subqueries.

[Ingolme]

I don't know, I'm just suggesting because I've never used subqueries, so I can't be sure exactly how they work. If performing the query before works ok then at least your script will be working.Maybe somebody else can find a different solution.

[Synook]

Trying echoing mysql_error() after performing the query. That will tell you what is wrong.

[justsomeguy]

You guys are funny, just trying stuff without looking up the syntax first. You don't use the equal sign with subqueries, you use IN.$sql1 = "SELECT * FROM db1.table1 WHERE field1 IN (SELECT post_id FROM db1.table2 WHERE field1=9795)";Also, make sure that the version of MySQL you're using supports subqueries. I believe they added that in version 4. If not, you can use a join instead.$sql1 = "SELECT db1.table1.* FROM db1.table1, db1.table2 WHERE db1.table1.field1=db1.table2.post_id AND db1.table2.field1=9795";

[SpOrTsDuDe.Reese]

Yet AGAIN. Justsomeguy has the correct answer.

[vchris]

Works! Thank you.