pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (3+ messages)

Message-ID: <web-814006@davinci.ethosmedia.com>
Permalink:  ../web-814006@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-814006@davinci.ethosmedia.com

 · 

reply

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