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 to
execute.

> 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 the
following?

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