Thread: help w/ constructing a SELECT

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

Charles,

> 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').

It appears that you haven't formulated clearly what you want to get.  I can
see 3 possibilities:

1. All contigs with one or more clones whose read = 'x' and those clones.
2. All contigs with one or more clones whose read = 'x' and all of those
contig's clones
3. All contigs where all clones have read = 'x'

Which do you want?

--
-Josh BerkusAglio Database SolutionsSan Francisco

Charles,

> > 3. All contigs where all clones have read = 'x'

SELECT * FROM contigs 
WHERE NOT EXISTS ( SELECT contig_idFROM clones WHERE clones.contig_id = contigs.contig_idAND read <> 'x');

i.e. "Select all contigs not having any clone whose read is something
other than 'x' "

got it?

-Josh Berkus