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.
