Mike, I recommend redesigning your query, the database can pull back the records you want far quicker than any frontend combining/processing, since that's what T-SQL and relational database were designed for. I might be able to help you with the SQL, but I am a little rusty, not really used it in 8 months. Maybe a MS-SQL/T-SQL forum will be able to help you with optimising your query to get what you need, they are usually very helpful. hth C. -----Original Message----- From: Mike Smith [mailto:mikeosmith@xxxxxxxxx] Sent: 20 July 2005 17:39 To: php-general@xxxxxxxxxxxxx Subject: Combining recordsets ************************************* This e-mail has been received by the Revenue Internet e-mail service. ************************************* I'm wondering if someone has a better solution then I've come up with. I've got a few reports that pull from various tables. Some times I'm not able to put all the tables into a single query/view. So I end up with a master query and 1 (or more) supporting queries. I'm using MSSQL 2000/IIS/PHP4/ADODB. This may be my own weakness in designing a query, I'm willing to learn if it is. So I've ended up doing things like: function formula($part,$array){ //imagine a formula to calculate weeks in pipe for a part foreach($array AS $val){ if($val[0]==$part){ $qty = $val[1]; } } return $qty; } $q1 = "SELECT part, fcast FROM parts"; $rs1 = $conn->Execute($s); $rs1_arr = $rs1->GetArray(); $q2 = "SELECT part, qty FROM pos"; $rs2 = $conn->Execute($s); $rs2_arr = $rs2->GetArray(); foreach($rs1_arr AS $data){ echo "Part: ".$data[0]; echo "Forecast: ".$data[1]; echo "POs: ".formula($data[0],$rs2_arr); } This is not a wonderful example, because I could LEFT JOIN pos to parts. But I have more complex queries that have several INNER/LEFT joins that once I bring in that last table to complete my query it (the last table) skews my sums. Really I'm just wondering if others have come across -- Mike -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ************************ This message has been delivered to the Internet by the Revenue Internet e-mail service ************************* -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
