SQL Query - Mailing list pgsql-general

From Scott Taylor
Subject SQL Query
Date
Msg-id 1038758006.1132.10.camel@localhost.localdomain
Whole thread Raw
List pgsql-general
That did not seem to work. In answer to a previous question, the
expected output should be all rows with trans_id's of 10088 (3 rows) and
10101 (2 rows), because these are the only trans_id's where chart_id
=10074 and also meet the other criteria.

Below is the query and output, which as you can see omits rows it should
have returned.

accs=# SELECT t2.*
accs-# FROM acc_trans AS t1,
accs-# acc_trans AS t2
accs-# WHERE
accs-# t1.chart_id = 10074
accs-# AND t1.trans_id = t2.trans_id
accs-# AND t2.amount >=0
accs-# AND t2.transdate BETWEEN '2002-07-01' AND '2002-09-30';

 trans_id | chart_id | amount  | transdate  | source | cleared |
----------+----------+---------+------------+--------+---------+----------------
    10088 |    10004 | 2062.12 | 2002-07-03 |        | f       | f
    10103 |    10004 |  528.75 | 2002-08-04 |        | f       | f
    10128 |    10037 |     450 | 2002-07-05 |        | f       | f
    10128 |    10074 |   78.75 | 2002-07-05 |        | f       | f
    10126 |    10004 |  528.75 | 2002-09-05 |        | f       | f
    10164 |    10037 |     525 | 2002-07-17 |        | f       | f
    10164 |    10074 |   91.88 | 2002-07-17 |        | f       | f
    10168 |    10037 |     525 | 2002-07-18 |        | f       | f
    10168 |    10074 |   91.88 | 2002-07-18 |        | f       | f
    10171 |    10029 |    23.5 | 2002-07-22 | J00012 | f       | f
    10172 |    10029 |   91.65 | 2002-07-22 | J00013 | f       | f
    10192 |    10074 |     3.5 | 2002-07-22 | J00032 | f       | f
    10192 |    10074 |     3.5 | 2002-07-22 | J00032 | f       | f
    10192 |    10057 |      20 | 2002-07-22 | J00032 | f       | f
    10192 |    10057 |      20 | 2002-07-22 | J00032 | f       | f
    10192 |    10074 |   13.65 | 2002-07-22 | J00032 | f       | f
    10192 |    10074 |   13.65 | 2002-07-22 | J00032 | f       | f
    10192 |    10058 |      78 | 2002-07-22 | J00032 | f       | f
    10192 |    10058 |      78 | 2002-07-22 | J00032 | f       | f
    10194 |    10037 |     525 | 2002-07-22 |        | f       | f
    10194 |    10074 |   91.88 | 2002-07-22 |        | f       | f
    10162 |    10004 |  555.19 | 2002-07-25 |        | f       | f
    10168 |    10004 |  616.88 | 2002-07-25 |        | f       | f
    10201 |    10037 |     525 | 2002-07-26 |        | f       | f
    10201 |    10074 |   91.88 | 2002-07-26 |        | f       | f
    10205 |    10037 |     420 | 2002-09-08 |        | f       | f
    10205 |    10074 |    73.5 | 2002-09-08 |        | f       | f
    10208 |    10037 |    2520 | 2002-09-08 |        | f       | f
    10208 |    10074 |     441 | 2002-09-08 |        | f       | f
    10164 |    10004 |  616.88 | 2002-07-31 |        | f       | f
    10194 |    10004 |  616.88 | 2002-07-31 |        | f       | f
    10201 |    10004 |  616.88 | 2002-07-31 |        | f       | f
    10203 |    10004 |  616.88 | 2002-08-15 |        | f       | f
    10205 |    10004 |   493.5 | 2002-08-15 |        | f       | f
    10208 |    10004 |    2961 | 2002-08-22 |        | f       | f

Regards

Scott



pgsql-general by date:

Previous
From: pginfo
Date:
Subject: Re: pg 7.3 , JDBC and timestamp problem.
Next
From: Tom Lane
Date:
Subject: Re: Segmentation fault while COPY in 7.3