help w/ constructing a SELECT
От | Charles Hauser |
---|---|
Тема | help w/ constructing a SELECT |
Дата | |
Msg-id | 1032446475.22067.29.camel@pandorina.biology.duke.edu обсуждение исходный текст |
Ответы |
Re: help w/ constructing a SELECT
|
Список | 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
В списке pgsql-sql по дате отправления: