Re: Problems Formulating a SELECT

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

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.
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

??

Charles

> >
> > 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
>
>