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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
