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.
Example:
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
TABLE contig:
contig_id 13805
assembly 20020630
ace 488
ver 1
CREATE TABLE clone (
clone_id SERIAL PRIMARY KEY,
project INTEGER REFERENCES library(project) NOT NULL,
plate INTEGER NOT NULL,
row CHAR(1) NOT NULL,
col INTEGER NOT NULL,
read CHAR(1) NOT NULL,
ver INTEGER NOT NULL,
seq TEXT NOT NULL,
L INTEGER NOT NULL,
Qvals TEXT NOT NULL,
TL INTEGER NOT NULL,
MQAT INTEGER NOT NULL,
Qstart INTEGER NOT NULL,
Qend INTEGER NOT NULL,
gb_id INTEGER REFERENCES gb(gb_id) NULL,
unigene BOOLEAN NULL,
UNIQUE (project,plate,row,col,read,ver)
);
CREATE TABLE contig (
contig_id SERIAL PRIMARY KEY,
assembly DATE NOT NULL,
ace INTEGER NOT NULL,
ver INTEGER NOT NULL,
length INTEGER NOT NULL,
seq TEXT NOT NULL,
UNIQUE (assembly,ace,ver)
);
CREATE TABLE clone_contig(
clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE,
contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,
UNIQUE(clone_id,contig_id)
);
regards,
Charles
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
