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

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

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

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
>
>
>
>
>

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

On Wed, 2007-01-17 at 11:47 -0600, Peter Brawley wrote:
> 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
This works!! It seems it was my defaults for my original table that was
causing me my problems. Sorry for troubling you, thanks once again for
the prompt solution.

Danny