sub SELECT

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