Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 42C3A64FCD7 for ; Fri, 25 Jul 2008 08:42:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14628-07 for ; Fri, 25 Jul 2008 08:42:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.168]) by postgresql.org (Postfix) with ESMTP id EC83F64FCF3 for ; Fri, 25 Jul 2008 08:42:40 -0300 (ADT) Received: by wf-out-1314.google.com with SMTP id 25so2669734wfc.28 for ; Fri, 25 Jul 2008 04:42:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=ym72qpnkTUm8p2AnKBTMBVYbT0Vt0PD/v96FSTEzFLI=; b=FoffKyOGN2BkHbM26plcY49gpS/v9lrqyl7JuSOBlXwIQGvIj0eyfEFwEBmeYg+TZ8 NBUg5kePy+3cOIq+xiCyzxByTslw8mAIgEUYgj96dCqMWgCfxkxBhEdwB8jubeSHiFXg AB3130X2sbSa5FkjQ+AxzE+ZYK3MIW85rYUao= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=Wl/2fepo5LKs1+mxc4iyhqGDo+2VMYHnd1VjncW1Z7mnrapP0nmtZlxXBt6f5htXTA +Ln14uvL537j39fsm992UAujrpVfiNUMMrRrCngTQcla9lA85ddRQYWS2XLGMTDF5e/n DHd+aehaHtRoCoMly/ooaJRZpWs4xwBbmrMGk= Received: by 10.142.242.11 with SMTP id p11mr508340wfh.217.1216986158959; Fri, 25 Jul 2008 04:42:38 -0700 (PDT) Received: by 10.143.18.15 with HTTP; Fri, 25 Jul 2008 04:42:38 -0700 (PDT) Message-ID: Date: Fri, 25 Jul 2008 13:42:38 +0200 From: "Gibson Chimhamhiwa" To: pgsql-admin@postgresql.org Subject: Prent-Child relationships in SQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12457_28822266.1216986158932" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/214 X-Sequence-Number: 29824 ------=_Part_12457_28822266.1216986158932 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi All, I have a simple table below and I want to be able to write a SQL select query that returns the grouping_ids until I get no more children. The parent_grouping_id column is the parent to the grouping_id child column. Can somebody please advise me how I can do this in postgreSQL. CREATE TABLE grouping ( grouping_id int4 NOT NULL, version int4, parent_grouping_id int4, topic_topic_id int4, CONSTRAINT grouping_pkey PRIMARY KEY (grouping_id), CONSTRAINT fk1e2e9d036852722d FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03a1bfbfe FOREIGN KEY (topic_topic_id) REFERENCES topic (topic_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk1e2e9d03cdaf7222 FOREIGN KEY (parent_grouping_id) REFERENCES grouping (grouping_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE grouping OWNER TO napdba; Thanks In Advance. ------=_Part_12457_28822266.1216986158932 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi All,

I have a simple table below and I want to be able to write a SQL select query that returns the grouping_ids until I get no more children. The parent_grouping_id column is the parent to the grouping_id child column. Can somebody please advise me how I can do this in postgreSQL.

CREATE TABLE grouping
(
  grouping_id int4 NOT NULL,
  version int4,
  parent_grouping_id int4,
  topic_topic_id int4,
  CONSTRAINT grouping_pkey PRIMARY KEY (grouping_id),
  CONSTRAINT fk1e2e9d036852722d FOREIGN KEY (topic_topic_id)
      REFERENCES topic (topic_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk1e2e9d03a1bfbfe FOREIGN KEY (topic_topic_id)
      REFERENCES topic (topic_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk1e2e9d03cdaf7222 FOREIGN KEY (parent_grouping_id)
      REFERENCES grouping (grouping_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE grouping OWNER TO napdba;

Thanks In Advance.

------=_Part_12457_28822266.1216986158932--