Re: SQL Query

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'