Re: SQL Query

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant