Re: [SQL] Getting one row for each subquery row...?

[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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])