From: Richard Huxton <dev@archonet.com>
To: Charles Hauser <chauser@duke.edu>
Cc: PSQL_list <pgsql-sql@postgresql.org>
Subject: Re: Problems Formulating a SELECT
Date: Tue, 8 Oct 2002 17:44:15 +0100
Message-ID: <200210081744.15596.dev@archonet.com> (raw)
In-Reply-To: <1034089459.4283.21.camel@pandorina.biology.duke.edu>
References: <1034018651.2282.38.camel@pandorina.biology.duke.edu>
<200210080938.37634.dev@archonet.com>
<1034089459.4283.21.camel@pandorina.biology.duke.edu>
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=> CREATE VIEW contig_x_vw AS SELECT contig_id FROM clone_contig,
> clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND
> read='x';
> CREATE
> chlamy_est=> CREATE VIEW contig_y_vw AS SELECT contig_id FROM clone_contig,
> clone WHERE chlamy_est-> clone_contig.clone_id = clone.clone_id AND
> read='y';
> CREATE
> chlamy_est=> SELECT contig_id FROM contig_x_vw, contig_y_vw WHERE
> contig_x_vw.clone_id = 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 you
will need to tell PG which one you want.
> If I understand the logic, you SELECT:
> all contig_ids where where clone.read = 'x' (VIEW contig_x_vw)
> all contig_ids where where clone.read = '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
isn't what's wanted you can check the columns below match. Otherwise, you're
quite right, that's what it's doing.
> cloneA.project=cloneB.project
> cloneA.plate=cloneB.plate
> cloneA.row=cloneB.row
> cloneA.col=cloneB.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
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: <200210081744.15596.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