Problems Formulating a SELECT - Mailing list pgsql-sql

From Charles Hauser
Subject Problems Formulating a SELECT
Date
Msg-id 1034018651.2282.38.camel@pandorina.biology.duke.edu
Whole thread Raw
Responses Re: Problems Formulating a SELECT
List 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 by date:

Previous
From: Joe Conway
Date:
Subject: Re: Get A Tree from a table
Next
From: Rafal Kedziorski
Date:
Subject: Debug information