Thread: sub SELECT

sub SELECT

From

Charles Hauser

Date:

01 March 2002, 11:59:07

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

Re: sub SELECT

From

"Josh Berkus"

Date:

01 March 2002, 15:46:18

Hey, Chuck!

How's the project going, so far?

> 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.

Why not a JOIN rather than a sub-select?  It would be faster toexecute.

> 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.

I think you're doing this the hard way.  What's wrong with thefollowing?

SELECT contig.assembly_date,contig.contig_no,contig.ver     FROM clone JOIN clone_contig USING (clone_id)    WHERE
clone.project= '1024' AND  clone.plate = '001' AND  clone.p_row = 'A' AND  clone.p_column = '01';
 

>      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'

If you want a subselect, then you want:
    WHERE  clone.clone_id  IN (  SELECT clone.clone_id  FROM clone  WHERE clone.project = '1024' AND   clone.plate =
'001'AND   clone.p_row = 'A' AND   clone.p_column = '01');
 

But I do not think that a subselect is actually what you need.

-Josh