Re: Problems Formulating a SELECT

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