X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 25C4230DE10 for ; Thu, 19 Jun 2003 18:38:36 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 04123-03 for ; Thu, 19 Jun 2003 15:38:04 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 9405530DBD7 for ; Thu, 19 Jun 2003 15:38:03 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 19T4IM-000BPa-0V; Thu, 19 Jun 2003 19:38:02 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 9669017A5C; Thu, 19 Jun 2003 19:38:01 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 0409317A51; Thu, 19 Jun 2003 19:38:00 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: =?iso-8859-1?q?Egy=FCd=20Csaba?= , pgsql-general@postgresql.org Subject: Re: SQL question Date: Thu, 19 Jun 2003 19:37:40 +0100 User-Agent: KMail/1.4.3 References: <000801c33680$e7f56a00$230a0a0a@compaq> In-Reply-To: <000801c33680$e7f56a00$230a0a0a@compaq> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200306191937.40883.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200306/1276 X-Sequence-Number: 44204 On Thursday 19 Jun 2003 5:36 pm, Egy=FCd Csaba wrote: > 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=3D# select t_stockchanges.productid, (select name from t_productgroups > where id=3D(select productgroupid from t_prod_in_pgr where > productid=3Dt_stockchanges.productid)) as pgroup from t_stockchanges; Try something like: SELECT chg.productid, grp.name as pgroup FROM t_stockchanges chg, t_prod_in_pgr pp, t_productgroups grp WHERE chg.productid=3Dpp.productid AND pp.productgroupid=3Dgrp.id; I might have got some of your fields wrong, but what I'm trying to do is jo= in=20 across the linked fields. change.product_id =3D> linktbl.product_id, linktbl.group_id =3D> groups.gro= up_id No need for a subselect here. --=20 Richard Huxton