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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
