extend "group by" to include "empty relations" ?

[peter pilsl <pilsl@xxxxxxxxxxxx>]

I've two tables related via a id-field.


           Table "public.fn_kat"
     Column      |            Type             |
-----------------+-----------------------------+-
 id              | integer                     |
 kategorie       | text                        |


           Table "public.fn_dokumente"
     Column      |            Type             |
-----------------+-----------------------------+-
 kategorie       | integer                     |
 content         | text                        |


I now want to list all entries in table fn_kat and count the number of
entries in fn_dokumente that have the actual id.


# select k.kategorie,count(d.oid) from fn_kat k,fn_dokumente d where
k.id=d.kategorie group by k.kategorie;

                kategorie                 | count
------------------------------------------+-------
 1. Forschungsnetzwerk Erwachsenenbildung |     1
 1.1. Protokolle                          |     3
 2. Sonstige Dokumente                    |     1


But there is a problem now: There are also entries in fn_kat which dont
have corresponding entries in fn_dokumente and this entries should be
listed too. With the proper count=0 !!

How to achieve this?

thnx a lot,
peter






-- 
mag. peter pilsl - goldfisch.at
IT-Consulting
Tel: +43-650-3574035
Tel: +43-1-8900602
Fax: +43-1-8900602-15
skype: peter.pilsl
pilsl@xxxxxxxxxxxx
www.goldfisch.at


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq