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

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: Együd Csaba <csegyud(at)freemail(dot)hu>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Getting one row for each subquery row...?
Date: 2003-06-20 06:11:58
Message-ID: 3EF2A5AE.1030006@klaster.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Együd Csaba 2003-06-20 07:02:58 Re: Getting one row for each subquery row...?
Previous Message Tomasz Myrta 2003-06-20 06:07:18 Re: Getting one row for each subquery row...?