Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Charles Hauser <chauser@duke.edu>,
	PSQL_list <pgsql-sql@postgresql.org>
Subject: Re: Problems Formulating a SELECT
Date: Tue, 8 Oct 2002 09:38:37 +0100
User-Agent: KMail/1.4.3
References: <1034018651.2282.38.camel@pandorina.biology.duke.edu>
In-Reply-To: <1034018651.2282.38.camel@pandorina.biology.duke.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210080938.37634.dev@archonet.com>

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=3D'y' and the other(s) have
> read=3D'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=3D'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=
=20
clone_config.clone_id =3D clone.clone_id AND read=3D'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 =
=3D=20
contig_y_vw.clone.id;

You don't need the views, but they make the example easier.

- Richard Huxton