On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote: > Daniel, > > >find the lab_number where ALL the tests have been performed and > >not the lab_numbers which have partial results. > > SELECT t1.lab,t1.res,t1.dt > FROM tbl t1 > WHERE NOT EXISTS( > SELECT lab FROM tbl t2 WHERE t1.lab=t2.lab AND t2.res IS NULL > ); > > The decorrelated version uses an exclusion join, runs faster but looks a > bit strange: > > SELECT t1.lab_number,t1.result,t1.release_time > FROM tbl t1 > LEFT JOIN tbl t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL > WHERE t2.lab_number IS NULL
Playing around with this examples, I realise I forgot to mention something I didn't make clear in my original post. The reason for doing the search is to find completed lab_numbers which have not yet been released, hence the 0000-00-00 00:00:00 timestamp. Using the examples you have given me don't seem to be working in the way I'd hoped. I'm just changing the "tbl" to the correct table name and seem to be getting results that are released, rows with no results and the completed results awaiting release. Using this query, based on yours: SELECT t1.lab_number,t1.result,t1.release_time FROM requesting_test_results t1 LEFT JOIN requesting_test_results t2 ON t1.lab_number=t2.lab_number AND t2.result IS NULL WHERE t2.lab_number IS NULL AND t1.result !='' AND t1.release_time =' 000-00-00 00:00:00' GROUP BY t1.lab_number I get 2 records, one is a lab_number which is completed but not released (lab_number 999), the other is a lab_number that has is only partially completed (3265). Removing the GROUP statement returns 6 records, four entries for 999 and 2 for 3265, though there are still 8 rows for 3265 with no result. I will try and get my head around joining to the same table in meantime. Thanks for prompt answer. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
