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
>
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
