From: | Charles Hauser <chauser(at)duke(dot)edu> |
---|---|
To: | PSQL_list <pgsql-sql(at)postgresql(dot)org> |
Subject: | help w/ constructing a SELECT |
Date: | 2002-09-19 14:41:15 |
Message-ID: | 1032446475.22067.29.camel@pandorina.biology.duke.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Greetings,
Having a problem with a query.
I would like to construct a query which will return a list of all
contigs which are comprised of clones whose 'read' = 'x' (read can be
either 'x' or 'y').
Details:
A contig may be comprised of more than 1 clone, so in TABLE
clone_contig, there may be multiple entries for a given contig as in:
chlamy_est=> select * from clone_contig;
clone_id | contig_id
----------+-----------
9811 | 1
82214 | 1
127472 | 1
82213 | 1
112644 | 1
9810 | 1
81641 | 2
This SELECT returns contigs comprised of clones whose reads are either
'x' or 'y'. Somehow I need an intersect or NOT comparrison???
SELECT contig.assembly,contig.ace,contig.ver
FROM clone JOIN clone_contig USING (clone_id)
JOIN contig USING (contig_id)
WHERE clone.read = 'x'
;
Tables:
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)
);
CREATE TABLE clone (
clone_id SERIAL PRIMARY KEY,
project INTEGER REFERENCES library(project) NOT NULL,
....snip....
read CHAR(1) NOT NULL,
....snip....
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mathieu Arnold | 2002-09-19 14:41:23 | Re: Stripping white-space in SELECT statments |
Previous Message | Stephan Szabo | 2002-09-19 14:34:27 | Re: Stripping white-space in SELECT statments |