From: Charles Hauser <chauser@acpub.duke.edu>
To: pgsql-sql@postgresql.org
Subject: sub SELECT
Date: Tue, 26 Feb 2002 15:42:38 -0500
Message-ID: <a05010408b8a19c0f5287@[152.3.11.193]> (raw)
Hi,
I think I should be able to implement a subquerie or subSELECT to
retrieve a set of data from my tables, but have not quite figured it
out. I would appreciate any ideas/suggestions.
Three TABLES involved: clone, clone_contig(relational) and contig.
Example:
clone.(project plate p_row p_column read ver) uniquely defines a
particular clone.
A search using 4 of these 6 columns [clone.(project plate p_row
p_column) = (894 001 A 01)] will return 2 results:
894 001 A 01 x 1
894 001 A 01 y 1
where 'x|y' = clone.read, and '1' = clone.ver.
For each of these two (894001A01x1, 894001A01y1), I want to find the
corresponding contig.
So, a two part query:
1st find ALL clones defined by clone.(project plate p_row p_column)
2nd find ALL contigs related to each clone.
The problem I have had is that the first/internal query returns
multiple values,
$result = $conn->exec(
"SELECT contig.assembly_date,contig.contig_no,contig.ver
FROM clone JOIN clone_contig USING (clone_id)
WHERE clone.clone_id = (
SELECT clone.clone_id
FROM clone
WHERE clone.project = '1024' AND
clone.plate = '001' AND
clone.p_row = 'A' AND
clone.p_column = '01'
)
");
CREATE TABLE "clone" (
"clone_id" int4 DEFAULT nextval('"clone_clone_id_seq"'::text) NOT NULL,
"project" int4 NOT NULL,
"plate" int4 NOT NULL,
"p_row" char(1) NOT NULL,
"p_column" int4 NOT NULL,
"read" char(1) NOT NULL,
"ver" int4 NOT NULL,
"seq" text NOT NULL,
"qual" text NOT NULL,
"length" int4 NOT NULL,
"qual_length" int4 NOT NULL,
"mean_qual_after_trim" int4 NOT NULL,
"qual_start" int4 NOT NULL,
"qual_end" int4 NOT NULL,
"comment" text NOT NULL,
"gb_accessions_id" int4,
CONSTRAINT "clone_pkey" PRIMARY KEY ("clone_id")
);
CREATE UNIQUE INDEX "clone_project_key" ON "clone" ("p_column",
"p_row", "plate", "project", "read", "ver");
CREATE TABLE "clone_contig" (
"clone_id" int4,
"contig_id" int4
);
CREATE UNIQUE INDEX "clone_contig_clone_id_key" ON "clone_contig"
("clone_id", "contig_id");
CREATE TABLE "contig" (
"contig_id" int4 DEFAULT nextval('"contig_contig_id_seq"'::text) NOT NULL,
"assembly_date" date NOT NULL,
"contig_no" int4 NOT NULL,
"ver" int4 NOT NULL,
"length" int4 NOT NULL,
"seq" text NOT NULL,
CONSTRAINT "contig_pkey" PRIMARY KEY ("contig_id")
);
CREATE UNIQUE INDEX "contig_assembly_date_key" ON "contig"
("assembly_date", "contig_no", "ver");
--
Regards,
Chuck
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chauser@acpub.duke.edu
Subject: Re: sub SELECT
In-Reply-To: <a05010408b8a19c0f5287@[152.3.11.193]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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