Thread: Getting one row for each subquery row...?

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.

Dnia 2003-06-20 07:12, Użytkownik Együd Csaba napisał:
> 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
> 
Sure, use "limit 1" in a subquery.

"limit" and "offset" are well described in Postgresql documentation.

Regards,
Tomasz Myrta

Dnia 2003-06-20 07:12, Użytkownik Együd Csaba napisał:

> 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
Sorry, if you want all combination of grups and products, you need to rewrite 
your query:

select t_stockchanges.productid, t_productgroups.name as pgroup
from t_stockchanges join t_prod_in using (productid) join t_productgroups on (id=productgroupid)

or something like this.

Tomasz

Thank you Tomasz,
this last one seems to be good - with a little change.

select distinct t_stockchanges.productid, t_productgroups.name as pgroup
from t_stockchanges
join t_prod_in_pgr using (productid)
join t_productgroups on (t_productgroups.id=productgroupid);

Thank you again.
By,
-- Csaba

> Sorry, if you want all combination of grups and products, you need to
rewrite
> your query:
>
> select
>   t_stockchanges.productid,
>   t_productgroups.name as pgroup
> from
>   t_stockchanges
>   join t_prod_in using (productid)
>   join t_productgroups on (id=productgroupid)
>
> or something like this.
>
> Tomasz
>



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