From: Peter Brawley
Date: January 17 2007 3:56pm
Subject: Re: Having problems with what appears to be a simple query.
List-Archive: http://lists.mysql.com/mysql/204469
Message-Id: <45AE4737.6050302@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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