On Monday 07 Oct 2002 8:24 pm, Charles Hauser wrote: > I am trying to formulate a SELECT and could use some suggestions. > > From the TABLES below I would like to find ALL contigs which contain the > same clones except that one (or more) has read='y' and the other(s) have > read='x'. > Or stated another way: find all contigs composed of (at least) both (x > and y) reads from the same clone. > > For example: > > 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
