SQL question

Hi All,
I have a problem. I have 3 tables.
1. stock changes
2. product groups
3. a link table between the 2 above

I need the name of the product group the product belongs to, which product
is the subject of the stock change. (I hope it's understandable)

So I tried this query:
----------------------------------------------------------------------------
------------------
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;
----------------------------------------------------------------------------
------------------

As I suppose this query should have produced 2 columns, the productid and
the related product group name: pgroup.

But instead it generated an error. It says, that the subquery (I suppose the
most inner) gives back more then 1 tuple. How can I query the only 1 record,
that matches the actual t-stockchanges records productid field. Isn't it
calculated for each stockchanges record?

Thank you in advance.

Best Regards,
-- Csaba

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