SQL Query

On 29 Nov 2002, 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

SELECT trans_id, chart_id, amount, transdate
FROM acc_trans
WHERE chart_id = 10074 AND
trans_id in (10088,10101) AND
amount >=0 AND
transdate BETWEEN '2002-07-01' AND '2002-09-30';

Tariq Muhammad
Liberty RMS
tariq@libertyrms.info
v:416-646-3304 x 111
c:416-993-1859
p:416-381-1457

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.

Ergh. Command history? You really should stick your queries somewhere
more permanent than that, even if it is one you only use in the query
monitor.

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

where all of these are true? where any of these are true?

1. If chart_id=10074, return all rows with same trans_id (i.e. trans_id
10088 and 10101)

Return all rows where there exists a row with the same trans_id and
chart_id = 10074. (That's easy to express with an "exists" where clause.)

Does the chart_id = 10074 row have to satisfy the two conditions below
for rows with the same trans_id to be returned? Or just the rows being
actually returned?

2. Where amount >=0
3. With transdate between 2002-07-01 and 2002-09-30

My best guess is that you are looking for this:

select trans_id,
chart_id,
amount,
transdate
from acc_trans
where exists (
select 'x'
from acc_trans sub
where acc_trans.trans_id = sub.trans_id
and sub.chart_id = 10074)
and amount >= 0
and transdate between '2002-07-01' and '2002-09-30'

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

On Sat, Nov 30, 2002 at 02:45:44AM -0500, Joel Burton wrote:

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'

Doesn't that need a "distinct" to be equivalent to the exists query? If
there are two 10074 rows with the same trans_id, I think all rows with
that trans_id would be returned twice otherwise.

--
Scott Lamb

On Sat, Nov 30, 2002 at 02:05:20AM -0600, Scott Lamb wrote:

On Sat, Nov 30, 2002 at 02:45:44AM -0500, Joel Burton wrote:

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'

Doesn't that need a "distinct" to be equivalent to the exists query? If
there are two 10074 rows with the same trans_id, I think all rows with
that trans_id would be returned twice otherwise.

Good catch, Scott. Yes, if you have another row with trans_id=10088 and
chart_id=10074, this row and the original-correct row will both show up
twice.

Adding DISTINCT will prevent that, but it's not perfect -- this would
suppress the case where two matching rows were in the table, while this
would appear in the IN or EXISTS cases. Which may or may not be a
problem, depending on the application. Of course, the best solution to
this would be to ensure that the table has a primary key, even if its
just a SERIAL column. Then we could DISTINCT w/o fear.

Ok, did I miss anything else? ;)

- J.
--

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