From: Richard Huxton <dev@archonet.com>
To: Charles Hauser <chauser@duke.edu>
To: PSQL_list <pgsql-sql@postgresql.org>
Subject: Re: Problems Formulating a SELECT
Date: Tue, 8 Oct 2002 09:38:37 +0100
Message-ID: <200210080938.37634.dev@archonet.com> (raw)
In-Reply-To: <1034018651.2282.38.camel@pandorina.biology.duke.edu>
References: <1034018651.2282.38.camel@pandorina.biology.duke.edu>
On Monday 07 Oct 2002 8:24 pm, Charles Hauser wrote:
> I am trying to formulate a SELECT and could use some suggestions.
>
> From the TABLES below I would like to find ALL contigs which contain the
> same clones except that one (or more) has read='y' and the other(s) have
> read='x'.
> Or stated another way: find all contigs composed of (at least) both (x
> and y) reads from the same clone.
>
> For example:
>
> In the data below, the contig '20020630.488.1'(contig_id:13805) is
> composed of 2 clones (clone_id='12018' and '12019') which are
> 894027G09.x and 894027G09.y, respectively.
>
> TABLE clone 'A' 'B'
>
> clone_id 12018 12019
> project 894 894
> plate 27 27
> row G G
> col 9 9
> read x y
>
> Table clone_contig:
>
> clone_id contig_id
> 12018 13805
> 12019 13805
How about something like:
CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_config, clone WHERE
clone_config.clone_id = clone.clone_id AND read='x';
CREATE VIEW contig_y_vw AS [same but for y]
SELECT config_id FROM contig_x_vw, contig_y_vw WHERE contig_x_vw.clone_id =
contig_y_vw.clone.id;
You don't need the views, but they make the example easier.
- Richard Huxton
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: dev@archonet.com, chauser@duke.edu
Subject: Re: Problems Formulating a SELECT
In-Reply-To: <200210080938.37634.dev@archonet.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