Re: SQL Query - Mailing list pgsql-general

On Fri, Nov 29, 2002 at 07:23:56PM +0000, Scott Taylor wrote:
> 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

It'd be helpful if you gave us the solution you expect for this sample
data, BTW.

Interpreting your question, I get:

  0) Find all trans_id #s where chart_id=10074
  1) Find all rows w/those trans_id where
      a) the amount >=0
      b) the date is between 7/1 and 9/30

so only the fourth record would be returned.


So something like:

SELECT *
FROM trans
WHERE
  trans_id IN (SELECT trans_id
               FROM   trans
           WHERE  chart_id = 10074 )
  AND amount >= 0
  AND transdate BETWEEN '2002-07-01' AND '2002-09-30'

would be the easiest-to-understand solution, but it won't perform
terribly well (because of the IN statement). You can rewrite this
w/EXISTS or with a multi-table join, and it should perform better:

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

but you should test w/your data and indexes to check performance.


--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant