Re: Problems Formulating a SELECT

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