Date: Fri, 20 Jun 2003 07:12:51 +0200
From: =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@freemail.hu>
Subject: Getting one row for each subquery row...?
To: pgsql-sql@postgresql.org
Reply-To: =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@freemail.hu>
Message-id: <007901c336ea$9cee0e80$230a0a0a@compaq>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

Hi All,
 here are my three tables. I wold like to list them in the following way: I
 need all the columns from t_stockchanges, and one field for the
productgroup
 the t_stockchanges.productid belongs to.

 But one product can belong to many groups, so i would need one row for each
 group for each product.

 My bad query is:
 ------------------------
 DB=# select t_stockchanges.productid, (select name from t_productgroups
 where id=(select productgroupid from t_prod_in_pgr where
 productid=t_stockchanges.productid)) as pgroup from t_stockchanges;
 ERROR:  More than one tuple returned by a subselect used as an expression.
 DB=#
 ---------------
 Yes, this is absolutelly true, but I would like postgres to give me all the
 tuples found. How can I ask him to do so?

 Thank you,
 -- Csaba

 ---------------------------------------------------------------------------
-
 ----------------------------------------------------
                              Table "public.t_stockchanges"
     Column     |       Type       |                      Modifiers
 ---------------+------------------+----------------------------------------
-
 ------------
  id            | integer          | not null
  stockid       | integer          | not null
  productid     | integer          | not null
  changeid      | integer          | not null
  quantity      | double precision | not null
  date          | character(19)    | not null
  purchaseprice | double precision | not null
  correction    | double precision | not null
  userid        | integer          | not null
  time          | character(19)    | default to_char(now(), 'YYYY.mm.dd
 hh:mi:ss'::text)
  prooftype     | character(10)    | not null default ''
  proofid       | integer          | default 0
 Indexes: t_stockchanges_pkey primary key btree (id),
          t_stockchanges_date btree (date),
          t_stockchanges_productid btree (productid)
 ---------------------------------------------------------------------------
-
 ----------------------------------------------------

          Table "public.t_productgroups"
    Column    |         Type          | Modifiers
 -------------+-----------------------+-----------
  id          | integer               | not null
  name        | character varying(30) | not null
  description | character varying     |
  root        | boolean               |
 Indexes: t_productgroups_pkey primary key btree (id)
 ---------------------------------------------------------------------------
-
 ----------------------------------------------------

      Table "public.t_prod_in_pgr"
      Column     |  Type   | Modifiers
 ----------------+---------+-----------
  productgroupid | integer | not null
  productid      | integer | not null
 ---------------------------------------------------------------------------
-
 ----------------------------------------------------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 2003. 06. 10.