Getting one row for each subquery row...? - Mailing list pgsql-sql

Hi All,here are my three tables. I wold like to list them in the following way: Ineed all the columns from
t_stockchanges,and one field for the
 
productgroupthe t_stockchanges.productid belongs to.
But one product can belong to many groups, so i would need one row for eachgroup for each product.
My bad query is:------------------------DB=# select t_stockchanges.productid, (select name from t_productgroupswhere
id=(selectproductgroupid from t_prod_in_pgr whereproductid=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
absolutellytrue, but I would like postgres to give me all thetuples 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 |
notnull userid        | integer          | not null time          | character(19)    | default to_char(now(),
'YYYY.mm.ddhh:mi:ss'::text)prooftype     | character(10)    | not null default '' proofid       | integer          |
default0Indexes: t_stockchanges_pkey primary key btree (id),         t_stockchanges_date btree (date),
t_stockchanges_productidbtree (productid)---------------------------------------------------------------------------
 
-----------------------------------------------------
         Table "public.t_productgroups"   Column    |         Type          |
Modifiers-------------+-----------------------+-----------id          | integer               | not null name        |
charactervarying(30) | not null description | character varying     | root        | boolean               |Indexes:
t_productgroups_pkeyprimary 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.