query problem - get count in related table

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: Postgresql Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: query problem - get count in related table
Date: 2002-05-23 17:31:04
Message-ID: 20020523173104.GA15057@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Thanks to everyone on the list I'm up and running with postgres.

I'm having trouble doing a query:

table a | table b
------------------------------
id data | id
------------------------------
1 2 | 2
2 1 | 2
3 4 | 1
| 1
| 1
| 3

I'd like to run a query that returned the following results:

a.id a.data count(b.id where b.id = a.data)
---------------------------------------------
1 2 2
2 1 3
3 4 0

Thanks for any help.
Rory

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Adam Erickson 2002-05-23 19:06:35 Re: when to use char, varchar or text
Previous Message Joel Burton 2002-05-23 16:45:06 Re: auto update dates