Re: sub SELECT

Hey, Chuck!

How's the project going, so far?

> A search using 4 of these 6 columns [clone.(project plate p_row
>  p_column) = (894 001 A 01)] will return 2 results:
>  894 001 A 01 x 1
>  894 001 A 01 y 1
> where 'x|y' = clone.read, and '1' = clone.ver.
> 
> For each of these two (894001A01x1, 894001A01y1), I want to find the
>  corresponding contig.

Why not a JOIN rather than a sub-select?  It would be faster toexecute.

> So, a two part query:
>  1st find ALL clones defined by clone.(project plate p_row p_column)
>  2nd find ALL contigs related to each clone.

I think you're doing this the hard way.  What's wrong with thefollowing?

SELECT contig.assembly_date,contig.contig_no,contig.ver     FROM clone JOIN clone_contig USING (clone_id)    WHERE
clone.project= '1024' AND  clone.plate = '001' AND  clone.p_row = 'A' AND  clone.p_column = '01';
 

>      WHERE  clone.clone_id = (
>    SELECT clone.clone_id
>    FROM clone
>    WHERE clone.project = '1024' AND
>    clone.plate = '001' AND
>    clone.p_row = 'A' AND
>    clone.p_column = '01'

If you want a subselect, then you want:
    WHERE  clone.clone_id  IN (  SELECT clone.clone_id  FROM clone  WHERE clone.project = '1024' AND   clone.plate =
'001'AND   clone.p_row = 'A' AND   clone.p_column = '01');
 

But I do not think that a subselect is actually what you need.

-Josh