reandeau

[!--quoteo(post=378795:date=May 31 2006, 11:21 AM:name=bill444)--][div class=\'quotetop\']QUOTE(bill444 @ May 31 2006, 11:21 AM) [snapback]378795[/snapback][/div][div class=\'quotemain\'][!--quotec--] I have two tables (mls and geodata) that each have mls numbers as primary key. I am trying to return all off the records in table mls where the mls number does not have a row in geodata. My current statement is returning too many records. I have 8 records in mls and 3 records in geodata (all of which match an mls # in table mls) so I should get 5 records returned but instead get 21 (8 * 3 minus the 3 matches). Can anyone help? [code]SELECT mls.mls_num, geodata.geodata_mls FROM mls, geodata WHERE mls.mls_num != geodata.geodata_mls ORDER BY mls_num ASC[/code] [/quote] Try something like this: SELECT mls.mls_num FROM mls LEFT JOIN geodata ON (mls.mls_num = geodata.geodata_mls) WHERE geodata.geodata_mls is NULL; Let me know how it goes. Jon Tjemsland

[!--quoteo(post=378785:date=May 31 2006, 10:52 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 10:52 AM) [snapback]378785[/snapback][/div][div class=\'quotemain\'][!--quotec--] Jon, Thanks for the reply. Currently there are close to 700,000 records in the table and it grows at 12000 records per test. I'm indexing an auto_increment field called "ID". Each set of 12000 (approximately) records has an ID called HEADER_ID tied to it. I think that's where the main time consumption is happening... doing the distinct on all of those. and $ttcode is "130,210" in this case. It's always going to be a very small comma seperated list of numbers (currently a max of 3). [/quote] Have you tried indexing the HEADER_ID field? Or could you create another table that would store the HEADER_IDs in it (pretty similar to just indexing)?

[!--quoteo(post=378744:date=May 31 2006, 08:39 AM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 31 2006, 08:39 AM) [snapback]378744[/snapback][/div][div class=\'quotemain\'][!--quotec--] I have the following select box that gets filled on a page load. Everything else on the page gets loaded almost instantly (I'm on a LAN), but when I get to this part of the page, it takes several very long seconds to fill the select box. I use almost the same code in another setup, so I think the PHP is fine. I'm pretty much narrowing it down to the actual query, but I thought I'd post here and see if anyone sees something wrong with how I'm processing it. And I know the query is pretty involved, so I'm wondering if I broke it up a bit whether it would be faster!? [code] <?php     $query = "SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader WHERE TEST_TYPE_CODE IN ($ttcode) AND HEADER_ID IN (SELECT DISTINCT HEADER_ID FROM TransientCycleData) ORDER BY END_DATE DESC";     $result = $ehandle->update_query($query);     if($result && mssql_num_rows($result) > 0)     {               // dump everything to the select box           $i = 0;         while($row = mssql_fetch_array($result))         {             extract($row);             $cell = $CELL_NUMBER;             if(strlen(trim($CELL_NUMBER)) == 1)                 $cell = "0" . $CELL_NUMBER;             echo "<option value=\"$HEADER_ID\">Cell $cell&nbsp;-&nbsp;" . @date("M d Y h:i A", strtotime($END_DATE)) . "&nbsp;-&nbsp;$TEST_NAME</option>";         }     }     ?> [/code] Oh, and the $ehandle->update_query() part is a custom class that I use. I know it's not that. [/quote] Its hard to know what would be slowing up your query without knowing how big your data set is and what you have indexed. You may want to try indexing some of the variables that you are trying to reference in the above query if they aren't already like HEADER_ID. What's in your $ttcode variable? Try doing a LEFT JOIN with TransientCycleData. Something like SELECT HEADER_ID, CELL_NUMBER, END_DATE, TEST_NAME FROM EngineTestHeader LEFT JOIN TransientCycleData ON (EngineTestHeader.HEADER_ID = TransientCycleData.HEADER_ID) Jon Tjemsland