Thread: 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
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