pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp w/ constructing a SELECT
3+ messages / 2 participants
[nested] [flat]
* help w/ constructing a SELECT
@ 2002-09-19 14:41 Charles Hauser <chauser@duke.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Charles Hauser @ 2002-09-19 14:41 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help w/ constructing a SELECT
@ 2002-09-19 23:40 Josh Berkus <josh@agliodbs.com>
parent: Charles Hauser <chauser@duke.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-09-19 23:40 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help w/ constructing a SELECT
@ 2002-09-20 16:38 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-09-20 16:38 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
Charles,
> > 3. All contigs where all clones have read = 'x'
SELECT * FROM contigs
WHERE NOT EXISTS ( SELECT contig_id
FROM clones WHERE clones.contig_id = contigs.contig_id
AND read <> 'x');
i.e. "Select all contigs not having any clone whose read is something
other than 'x' "
got it?
-Josh Berkus
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-20 16:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-19 14:41 help w/ constructing a SELECT Charles Hauser <chauser@duke.edu>
2002-09-19 23:40 ` Josh Berkus <josh@agliodbs.com>
2002-09-20 16:38 Re: help w/ constructing a SELECT Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox