[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: [SQL] sub SELECT
From: Charles Hauser <chauser () acpub ! duke ! edu>
Date: 2002-02-26 20:42:38
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<!doctype html public "-//W3C//DTD W3 HTML//EN">
<html><head><style type="text/css"><!--
blockquote, dl, ul, ol, li { margin-top: 0 ; margin-bottom: 0 }
--></style><title>sub SELECT</title></head><body>
<div>Hi,</div>
<div><br></div>
<div>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.</div>
<div><br></div>
<div>Three TABLES involved: clone, clone_contig(relational) and
contig.</div>
<div><br></div>
<div><br></div>
<div>Example:</div>
<div><br></div>
<div>clone.(project plate p_row p_column read ver) uniquely defines a
particular clone.</div>
<div><br></div>
<div>A search using 4 of these 6 columns [clone.(project plate p_row
p_column) = (894 001 A 01)] will return 2 results:</div>
<div><x-tab> </x-tab>894 001
A 01<font color="#FF0000"> x 1</font></div>
<div><x-tab> </x-tab>894 001
A 01<font color="#FF0000"> y 1</font></div>
<div>where '<font color="#FF0000">x|y</font>' = clone.read, and
'<font color="#FF0000">1</font>' = clone.ver.</div>
<div><br></div>
<div>For each of these two (894001A01x1, 894001A01y1), I want to find
the corresponding contig.</div>
<div><br></div>
<div><br></div>
<div>So, a two part query:</div>
<div><x-tab> </x-tab>1st
find ALL clones defined by clone.(project plate p_row p_column)</div>
<div><x-tab> </x-tab>2nd
find ALL contigs related to each clone.</div>
<div><br></div>
<div><br></div>
<div>The problem I have had is that the first/internal query returns
multiple values,</div>
<div> $result = $conn->exec(</div>
<div> "SELECT
contig.assembly_date,contig.contig_no,contig.ver<br>
FROM clone JOIN clone_contig USING
(clone_id)</div>
<div> WHERE clone.clone_id = (<br>
<x-tab> </x-tab><x-tab>
</x-tab><x-tab>
</x-tab>SELECT clone.clone_id<br>
<x-tab>
</x-tab><x-tab>
</x-tab><x-tab> </x-tab>FROM
clone<br>
<x-tab>
</x-tab><x-tab>
</x-tab><x-tab>
</x-tab>WHERE clone.project = '1024' AND<br>
<x-tab>
</x-tab><x-tab>
</x-tab><x-tab>
</x-tab>clone.plate = '001' AND<br>
<x-tab> </x-tab><x-tab>
</x-tab><x-tab>
</x-tab>clone.p_row = 'A' AND<br>
<x-tab>
</x-tab><x-tab>
</x-tab><x-tab>
</x-tab>clone.p_column = '01'<br>
<x-tab>
</x-tab><x-tab>
</x-tab><x-tab>
</x-tab>)<br>
");<br>
</div>
<div><br></div>
<div><br></div>
<div><br></div>
<div>CREATE TABLE "clone" (<br>
"clone_id" int4 DEFAULT
nextval('"clone_clone_id_seq"'::text) NOT NULL,</div>
<div> "project" int4 NOT NULL,<br>
"plate" int4 NOT NULL,<br>
"p_row" char(1) NOT NULL,<br>
"p_column" int4 NOT NULL,<br>
"read" char(1) NOT NULL,<br>
"ver" int4 NOT NULL,</div>
<div> "seq" text NOT NULL,<br>
"qual" text NOT NULL,<br>
"length" int4 NOT NULL,<br>
"qual_length" int4 NOT NULL,<br>
"mean_qual_after_trim" int4 NOT NULL,<br>
"qual_start" int4 NOT NULL,<br>
"qual_end" int4 NOT NULL,<br>
"comment" text NOT NULL,<br>
"gb_accessions_id" int4,<br>
CONSTRAINT "clone_pkey" PRIMARY KEY
("clone_id")<br>
);<br>
</div>
<div><br></div>
<div>CREATE UNIQUE INDEX "clone_project_key" ON
"clone" ("p_column", "p_row",
"plate", "project", "read",
"ver");<br>
<br>
CREATE TABLE "clone_contig" (<br>
"clone_id" int4,<br>
"contig_id" int4<br>
);<br>
CREATE UNIQUE INDEX "clone_contig_clone_id_key" ON
"clone_contig" ("clone_id",
"contig_id");<br>
</div>
<div><br></div>
<div>CREATE TABLE "contig" (<br>
"contig_id" int4 DEFAULT
nextval('"contig_contig_id_seq"'::text) NOT NULL,<br>
"assembly_date" date NOT NULL,<br>
"contig_no" int4 NOT NULL,<br>
"ver" int4 NOT NULL,<br>
"length" int4 NOT NULL,<br>
"seq" text NOT NULL,<br>
CONSTRAINT "contig_pkey" PRIMARY KEY
("contig_id")<br>
);<br>
CREATE UNIQUE INDEX "contig_assembly_date_key" ON
"contig" ("assembly_date", "contig_no",
"ver");<br>
</div>
<div>-- <br>
Regards,<br>
<br>
<x-tab> </x-tab>Chuck</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic