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
