Re: Having problems with what appears to be a simple query.
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 There's a bit of discussion of this query pattern at http://www.artfulsoftware.com/queries.php nder 'Joins', 'The [Not] Exists Query Pattern'. PB - Daniel Smith wrote: I have a table with numerous columns but needing to perform a query based on three columns: Lab_number, result, release_time. What I want to do is search for lab_number where there is a result but not released. The problem that is making this difficult for me, is that there are multiple entries for the same lab_number, due to a lab_number having 1 or more tests being performed on it. The search I really want to do is find the lab_number where ALL the tests have been performed and not the lab_numbers which have partial results. *---* |lab_number | result |release_time | | 1 | 10 |-00-00 00:00:00| | 1 | 20 |-00-00 00:00:00| | 2 | 5 |-00-00 00:00:00| | 2 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| | 3 | |-00-00 00:00:00| *---* So the query I want will only return 1, as 2 is not yet complete. The attempts I have made so far will return 2 as well. The thing that is really annoying me is that I know I will kick myself when I see a solution! Thanks Danny -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
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 -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]
Re: Having problems with what appears to be a simple query.
Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ 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 t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB Daniel Smith wrote: 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 -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 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.13/632 - Release Date: 1/16/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having problems with what appears to be a simple query.
On Wed, 2007-01-17 at 11:47 -0600, Peter Brawley wrote: Daniel, CREATE TABLE tbl ( lab_number int(11) default NULL, result int(11) default NULL, release_time datetime default NULL ); select * from tbl; +++-+ | lab_number | result | release_time| +++-+ | 1 | 10 | 2007-01-17 00:00:00 | | 1 | 20 | 2007-01-17 00:00:00 | | 2 | 5 | 2007-01-17 00:00:00 | | 2 | NULL | -00-00 00:00:00 | | 3 | NULL | -00-00 00:00:00 | | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ 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 t1.release_time=0 AND t2.lab_number IS NULL +++-+ | lab_number | result | release_time| +++-+ | 4 | 25 | -00-00 00:00:00 | | 4 | 35 | -00-00 00:00:00 | +++-+ PB This works!! It seems it was my defaults for my original table that was causing me my problems. Sorry for troubling you, thanks once again for the prompt solution. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
