Re: Problems Formulating a SELECT - Mailing list pgsql-sql

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 ANDcontig_x_vw.plate = contig_y_vw.plate ANDcontig_x_vw.col =
contig_y_vw.colANDcontig_x_vw.row = contig_y_vw.row ANDcontig_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
> 
>