Getting all rows even if not a member of any groups - Mailing list pgsql-sql

From Együd Csaba
Subject Getting all rows even if not a member of any groups
Date
Msg-id 00bd01c33c9d$1b584e90$230a0a0a@compaq
Whole thread Raw
In response to Rules: passing new/old to functions  (Gunter Diehl <gdid@gmx.de>)
Responses Re: Getting all rows even if not a member of any groups
List pgsql-sql
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.



pgsql-sql by date:

Previous
From: Tomasz Myrta
Date:
Subject: Re: Postgres - Delphi Application
Next
From: Bruno Wolff III
Date:
Subject: Re: Getting all rows even if not a member of any groups