From: | "Josh Berkus" <josh(at)agliodbs(dot)com> |
---|---|
To: | Charles Hauser <chauser(at)acpub(dot)duke(dot)edu>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: sub SELECT |
Date: | 2002-03-01 17:42:35 |
Message-ID: | web-814006@davinci.ethosmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Lebedev | 2002-03-01 20:21:51 | sequential joins |
Previous Message | Josh Berkus | 2002-03-01 17:36:12 | Re: About persistent connections... |