SQL Query

I have submitted this query to the list before, but have since upgraded
to a later version so I lost the command history.

From the below output, could someone tell me how to return rows only
where:

1. If chart_id=10074, return all rows with same trans_id (i.e. trans_id
10088 and 10101)
2. Where amount >=0
3. With transdate between 2002-07-01 and 2002-09-30

accs=# select trans_id, chart_id, amount, transdate from acc_trans;
trans_id | chart_id | amount | transdate
----------+----------+----------+------------
10088 | 10004 | -2062.12 | 2002-01-03
10088 | 10037 | 1755 | 2002-01-03
10088 | 10074 | 307.12 | 2002-01-03
10088 | 10004 | 2062.12 | 2002-07-03
10088 | 10002 | -2062.12 | 2002-07-03
10096 | 10016 | 1169.75 | 2002-12-03
10096 | 10047 | -1169.75 | 2002-12-03
10096 | 10002 | 1169.75 | 2002-11-03
10096 | 10016 | -1169.75 | 2002-11-03
10098 | 10016 | 283.91 | 2002-12-03
10098 | 10044 | -283.91 | 2002-12-03
10099 | 10016 | 137.6 | 2002-12-03
10099 | 10045 | -137.6 | 2002-12-03
10100 | 10016 | 163.74 | 2002-12-03
10100 | 10046 | -163.74 | 2002-12-03
10101 | 10004 | -528.75 | 2002-03-20
10101 | 10037 | 450 | 2002-03-20
10101 | 10074 | 78.75 | 2002-03-20

Thanks

Scott