Problems Formulating a SELECT

Поиск
Список
Период
Сортировка
От Charles Hauser
Тема Problems Formulating a SELECT
Дата
Msg-id 1034018651.2282.38.camel@pandorina.biology.duke.edu
обсуждение исходный текст
Ответы Re: Problems Formulating a SELECT
Список pgsql-sql
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_id12018        1380512019        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




В списке pgsql-sql по дате отправления:

Предыдущее
От: Joe Conway
Дата:
Сообщение: Re: Get A Tree from a table
Следующее
От: Rafal Kedziorski
Дата:
Сообщение: Debug information