pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (3+ messages)  latest in thread

Message-ID: <a05010408b8a19c0f5287@[152.3.11.193]>
Permalink:  ../a05010408b8a19c0f5287@%5B152.3.11.193%5D/
Also on:    postgresql.org/message-id/a05010408b8a19c0f5287@[152.3.11.193]

 · 

reply

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