X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id B8D3331020F for ; Fri, 27 Jun 2003 11:13:56 +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 52375-06 for ; Fri, 27 Jun 2003 08:13:46 -0300 (ADT) Received: from vadangyal.vnet.hu (mail.vnet.hu [213.163.59.4]) by svr1.postgresql.org (Postfix) with ESMTP id E52B23101FE for ; Fri, 27 Jun 2003 08:13:45 -0300 (ADT) Received: from compaq (adsl213069.vnet.hu [62.77.213.69]) by mail.vnet.hu (iPlanet Messaging Server 5.1 Patch 1 (built Jun 6 2002)) with ESMTPA id <0HH500E1F0IWP8@mail.vnet.hu> for pgsql-sql@postgresql.org; Fri, 27 Jun 2003 13:13:45 +0200 (CEST) Date: Fri, 27 Jun 2003 13:13:07 +0200 From: =?iso-8859-1?Q?Egy=FCd_Csaba?= Subject: Getting all rows even if not a member of any groups To: pgsql-sql@postgresql.org Reply-To: =?iso-8859-1?Q?Egy=FCd_Csaba?= Message-id: <00bd01c33c9d$1b584e90$230a0a0a@compaq> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Mailer: Microsoft Outlook Express 6.00.2720.3000 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 8BIT X-Priority: 3 X-MSMail-priority: Normal References: <11362.1056313692@www59.gmx.net> X-Archive-Number: 200306/311 X-Sequence-Number: 14069 Hi, I have a product table identified by its id field. There is a productgroups table with productisd, productgroupid fields. And I have a prod_in_pgr (productid, productgroupid) table which describes the membership of productgroups. Each product can be a member of zero or more productgroups, but one productgroup can contain a product only once. I would like to list the following information: productgroupid | productid | ... some other prouduct info | ... I need all the products even if it is not a member in any productgroups. I need these information ordered by productgroup and then productid. -------------------------------- An example: select t_productgroups.name as pgroup, t_products.id as productid from t_products join t_prod_in_pgr on (t_products.id=productid) join t_productgroups on (t_productgroups.id = t_prod_in_pgr.productgroupid) order by pgroup, productid; --------------------> pgroup | productid ----------------------+----------- Alumínium profilok | 6047 Alumínium profilok | 6048 Alumínium profilok | 6049 Alumínium profilok | 6050 Alumínium profilok | 6051 Alumínium profilok | 6052 Alumínium profilok | 6053 Alumínium profilok | 6054 Alumínium profilok | 6055 Alumínium profilok | 6056 Alumínium profilok | 6057 Alumínium profilok | 6058 Alumínium profilok | 6059 Alumínium profilok | 6060 Alumínium profilok | 6061 Alumínium profilok | 6062 Gumik | 6063 Hohíd mentes profilok | 6060 Hohíd mentes profilok | 6061 Hohíd mentes profilok | 6062 Hohidas profilok | 6050 Hohidas profilok | 6051 Hohidas profilok | 6052 Hohidas profilok | 6053 Hohidas profilok | 6054 Hohidas profilok | 6055 Hohidas profilok | 6056 Hohidas profilok | 6057 Hohidas profilok | 6058 Hohidas profilok | 6059 Nyílászárók | 6064 -------------------------------------------------------- I hope it is understandable. This query is a result of a 'join'-ed query (see above), but it can contain only those products which are in one or more groups. But I also need the ungroupd items. Pleas tell me how to create such an sql query. Thank you, -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.