On Wed, 2007-01-17 at 09:56 -0600, Peter Brawley wrote:
> >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:
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
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.