List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 17 2007 5:47pm
Subject:Re: Having problems with what appears to be a simple query.
View as plain text  
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 | 0000-00-00 00:00:00 |
|          3 |   NULL | 0000-00-00 00:00:00 |
|          4 |     25 | 0000-00-00 00:00:00 |
|          4 |     35 | 0000-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 | 0000-00-00 00:00:00 |
|          4 |     35 | 0000-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 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
>
>
>
>
>   

Attachment: [text/html]
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
Thread
Having problems with what appears to be a simple query.Daniel Smith17 Jan
  • Re: Having problems with what appears to be a simple query.Peter Brawley17 Jan
    • Re: Having problems with what appears to be a simple query.Daniel Smith17 Jan
      • Re: Having problems with what appears to be a simple query.Peter Brawley17 Jan
        • Re: Having problems with what appears to be a simple query.Daniel Smith17 Jan