From: Josh Berkus <josh@agliodbs.com>
To: Charles Hauser <chauser@acpub.duke.edu>
To: pgsql-sql@postgresql.org
Subject: Re: sub SELECT
Date: Fri, 01 Mar 2002 09:42:35 -0800
Message-ID: <web-814006@davinci.ethosmedia.com> (raw)
In-Reply-To: <a05010408b8a19c0f5287@[152.3.11.193]>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, chauser@acpub.duke.edu
Subject: Re: sub SELECT
In-Reply-To: <web-814006@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox