Received: from wilson.acpub.duke.edu (wilson.acpub.duke.edu [152.3.233.69]) by postgresql.org (Postfix) with ESMTP id 82EAD4768B1 for ; Tue, 26 Feb 2002 15:42:42 -0500 (EST) Received: from [152.3.11.193] (bglab2.botany.duke.edu [152.3.11.193]) by wilson.acpub.duke.edu (8.11.5/8.11.3/Duke-5.0.0) with ESMTP id g1QKgfX07982; Tue, 26 Feb 2002 15:42:41 -0500 (EST) Mime-Version: 1.0 X-Sender: chauser@mail-ch.acpub.duke.edu Message-Id: Date: Tue, 26 Feb 2002 15:42:38 -0500 To: pgsql-sql@postgresql.org From: Charles Hauser Subject: sub SELECT Content-Type: multipart/alternative; boundary="============_-1197366331==_ma============" X-Virus-Scanned: by amavisd-milter, Duke University (http://amavis.org/) X-Archive-Number: 200203/2 X-Sequence-Number: 6884 --============_-1197366331==_ma============ Content-Type: text/plain; charset="us-ascii" ; format="flowed" 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 --============_-1197366331==_ma============ Content-Type: text/html; charset="us-ascii" 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
--============_-1197366331==_ma============--