From: Együd Csaba <csegyud@freemail.hu>
To: pgsql-sql@postgresql.org
Subject: Getting all rows even if not a member of any groups
Date: Fri, 27 Jun 2003 13:13:07 +0200
Message-ID: <00bd01c33c9d$1b584e90$230a0a0a@compaq> (raw)
References: <11362.1056313692@www59.gmx.net>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: csegyud@freemail.hu
Subject: Re: Getting all rows even if not a member of any groups
In-Reply-To: <00bd01c33c9d$1b584e90$230a0a0a@compaq>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox