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

On Tuesday 08 Oct 2002 4:04 pm, Charles Hauser wrote:
> Richard,
>
> Thanks, a followup.
>
> I believe this will not work (novice, so take w/ grain of salt).
>
> I tried the following:
>
> chlamy_est=3D> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_con=
tig,
> clone WHERE chlamy_est-> clone_contig.clone_id =3D clone.clone_id AND
> read=3D'x';
> CREATE
> chlamy_est=3D> CREATE VIEW contig_y_vw AS SELECT contig_id FROM clone_con=
tig,
> clone WHERE chlamy_est-> clone_contig.clone_id =3D clone.clone_id AND
> read=3D'y';
> CREATE
> chlamy_est=3D> SELECT contig_id FROM contig_x_vw, contig_y_vw WHERE
> contig_x_vw.clone_id =3D chlamy_est-> contig_y_vw.clone.id;
> ERROR:  Column reference "contig_id" is ambiguous

My bad, I didn't test it - for the last one you'll want

SELECT contig_x_vw.contig_id FROM contig_x_vw ...

It doesn't matter which id you use (since you want the value in each) but y=
ou=20
will need to tell PG which one you want.

> If I understand the logic, you SELECT:
> 	all contig_ids where where clone.read =3D 'x' (VIEW contig_x_vw)
> 	all contig_ids where where clone.read =3D 'y' (VIEW contig_y_vw)
> 	find isect of these two wehere their clone_ids are same
>
>
> However, their clone_ids will never be the same as in the example.

It's checking the "contig_id"s are the same (from clone_contig) - if that=
=20
isn't what's wanted you can check the columns below match. Otherwise, you'r=
e=20
quite right, that's what it's doing.

> cloneA.project=3DcloneB.project
> cloneA.plate=3DcloneB.plate
> cloneA.row=3DcloneB.row
> cloneA.col=3DcloneB.col
>
>
> TABLE clone      'A'    'B'
>
> clone_id        12018   12019
> project         894     894
> plate           27      27
> row             G       G
> col             9       9
> read            x       y

Does that get you any further? If not, I'm back at my desk tomorrow 9am GMT

- Richard Huxton