Re: Problems Formulating a SELECT

Richard,

Thanks again.

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

No problem, after I sent the email I caught the source of the ' "contig_id" is ambiguous' ERROR.

>
> It's checking the "contig_id"s are the same (from clone_contig) - ....

A contig_id match is what I wanted, but also a requirement that
clone.project,plate,col & row also match for cloneA and cloneB. I added
what I think gives me the correct match params (see below) and a quick
survey of the results looks to be ok.

Is there a method to remove duplicate results? For instance the query
below in part yields :

chlamy_est-> ;
contig_id
-----------
27170
27173
27173
27179
27179
27179
27179
27179

The repetitive occurrence of the same contig_id (27179) occurs as a
result of multiple pairs of clones matching the criteria for a given
contig_id. So for contig_id 27179 there are 5 pairs which match:

cloneA cloneB contig_id
894018D05.x1 <-> 894018D05.y1 27179
894080H12.x1 <-> 894080H12.y1 27179
894094E04.x2 <-> 894094E04.y2 27179
894095H03.x1 <-> 894095H03.y2 27179
963037B05.x2 <-> 963037B05.y1 27179

CREATE VIEW contig_x_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE
clone_contig.clone_id = clone.clone_id AND read='x';

CREATE VIEW contig_y_vw AS SELECT contig_id,project,plate,row,col FROM clone_contig, clone WHERE
clone_contig.clone_id = clone.clone_id AND read='y';

SELECT contig_x_vw.contig_id FROM contig_x_vw, contig_y_vw
WHERE contig_x_vw.project = contig_y_vw.project AND
contig_x_vw.plate = contig_y_vw.plate AND
contig_x_vw.col = contig_y_vw.col AND
contig_x_vw.row = contig_y_vw.row AND
contig_x_vw.contig_id = contig_y_vw.contig_id
;

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