Nested queries with ADODB

[bipster]

Hi,

I'm a newbie regarding the ADODB lib and I'm having problems doing simple nested queries with it.

$rs = $DB->Execute("select * from TABLE1");
while (!$rs->EOF) {

$rs2 = $DB->Execute("select * from TABLE2 where field=".$rs->fields["id"]);
while (!$rs2->EOF) {
  echo $rs2->fields;
  $rs2->MoveNext();
}
$rs2->Close();

$rs->MoveNext();
}
$rs->Close();

Although I'm closing the $rs2 resultset at the end of the loop, it seems that ADODB is caching it. Is there a way of cleaning it up to be able to reuse the same variable?

Thanks in advance

[Piranha]

You would be much better off doing a join:

$sql = "select columns from table1 left join table2 on table1.id = table2.table1id";

Then you only have to do one database request instead of the first one plus one for each row in that result.