Q:

suggestions on query to get counts from other tables on a postgres db

I am trying to create a query that returns a list of members with counts from two secondary tables.
I have the members table with multiple members that share the same parent_id.
I have two other tables that for each member has multiple entries. The results i am trying to produce is a list of members for a particular parent_id and for each member a count of records from table2 and table3
The result would look like
member_id, parent_id, name, count(table2.member_id ), count(table3.member_id)
thanks for any help
table members

member_id   int primary key
parent_id   int  
name        varchar

table 2    ( member_id will have multiple records for each member )

t2_id      int primary key
member_id  int
data_2     int

table 3    ( member_id will have multiple records )

t3_id      int primary key
member_id  int
data_3     int

A:

You can use correlated subqueries:
select m.*,
       (select count(*) from table2 t2 where t2.member_id = m.member_id
       ) as num_t2,
       (select count(*) from table3 t3 where t3.member_id = m.member_id
       ) as num_t3
from members m;

