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 |0000-00-00 00:00:00 |
| 1 | 20 |0000-00-00 00:00:00 |
| 2 | 5 |0000-00-00 00:00:00 |
| 2 | |0000-00-00 00:00:00 |
| 3 | |0000-00-00 00:00:00 |
| 3 | |0000-00-00 00:00:00 |
| 3 | |0000-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]
