Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT Query

SELECT Query

From
Scott Taylor
Date:
Below is the result of a query on my table. I want to only return those
transactions that have a chart_id=10074, and if trans_id 10088 is one of
those, I want to return all 3 rows with that trans_id.

accs=# select * from acc_trans;
 trans_id | chart_id |  amount  | transdate  |    source    | cleared |
fx_transaction
----------+----------+----------+------------+--------------+---------+----------------
    10088 |    10004 | -2062.12 | 2002-03-01 |              | f       | f
    10088 |    10037 |     1755 | 2002-03-01 |              | f       | f
    10088 |    10074 |   307.12 | 2002-03-01 |              | f       | f
    10092 |    10004 |  -528.75 | 2002-03-12 |              | f       | f
    10092 |    10037 |      450 | 2002-03-12 |              | f       | f
    10092 |    10074 |    78.75 | 2002-03-12 |              | f       | f
    10088 |    10004 |  2062.12 | 2002-03-07 |              | f       | f
    10088 |    10002 | -2062.12 | 2002-03-07 |              | f       | f
    10096 |    10016 |  1169.75 | 2002-03-12 |              | f       | f
    10096 |    10047 | -1169.75 | 2002-03-12 |              | f       | f
    10096 |    10002 |  1169.75 | 2002-03-11 |              | f       | f
    10096 |    10016 | -1169.75 | 2002-03-11 |              | f       | f
    10098 |    10016 |   283.91 | 2002-03-12 |              | f       | f
    10098 |    10044 |  -283.91 | 2002-03-12 |              | f       | f
    10099 |    10016 |    137.6 | 2002-03-12 |              | f       | f
    10099 |    10045 |   -137.6 | 2002-03-12 |              | f       | f
    10100 |    10016 |   163.74 | 2002-03-12 |              | f       | f
    10100 |    10046 |  -163.74 | 2002-03-12 |              | f       | f
    10092 |    10004 |   528.75 | 2002-03-20 |              | f       | f
    10092 |    10002 |  -528.75 | 2002-03-20 |              | f       | f
    10101 |    10004 |  -528.75 | 2002-03-20 |              | f       | f
    10101 |    10037 |      450 | 2002-03-20 |              | f       | f
    10101 |    10074 |    78.75 | 2002-03-20 |              | f       | f

Regards

Scott

Re: SELECT Query

From
Martijn van Oosterhout
Date:
On Fri, Nov 01, 2002 at 11:58:23AM +0000, Scott Taylor wrote:
> Below is the result of a query on my table. I want to only return those
> transactions that have a chart_id=10074, and if trans_id 10088 is one of
> those, I want to return all 3 rows with that trans_id.

Hmm...

select * from acc_trans where trans_id in
    (select trans_id from acc_trans where chart_id = 10074);

You can rewrite that as an EXISTS query, but it should work fine.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
Attachment

Re: SELECT Query

From
Richard Huxton
Date:
On Friday 01 Nov 2002 11:58 am, Scott Taylor wrote:
> Below is the result of a query on my table. I want to only return those
> transactions that have a chart_id=10074, and if trans_id 10088 is one of
> those, I want to return all 3 rows with that trans_id.

Also, check the pgsql-sql list for the question yesterday "How do you write
this query?" posted by Wei Weng.

--
  Richard Huxton

Re: SELECT Query

From
Jean-Luc Lachance
Date:
select * from acc_trans where chart_id = 10074
union
select * from acc_trans where tx_transaction = 10088
  and exists ( select * from acc_trans where tx_transaction = 10088 and
chart_id = 10074);


Scott Taylor wrote:
>
> Below is the result of a query on my table. I want to only return those
> transactions that have a chart_id=10074, and if trans_id 10088 is one of
> those, I want to return all 3 rows with that trans_id.
>
> accs=# select * from acc_trans;
>  trans_id | chart_id |  amount  | transdate  |    source    | cleared |
> fx_transaction
> ----------+----------+----------+------------+--------------+---------+----------------
>     10088 |    10004 | -2062.12 | 2002-03-01 |              | f       | f
>     10088 |    10037 |     1755 | 2002-03-01 |              | f       | f
>     10088 |    10074 |   307.12 | 2002-03-01 |              | f       | f
>     10092 |    10004 |  -528.75 | 2002-03-12 |              | f       | f
>     10092 |    10037 |      450 | 2002-03-12 |              | f       | f
>     10092 |    10074 |    78.75 | 2002-03-12 |              | f       | f
>     10088 |    10004 |  2062.12 | 2002-03-07 |              | f       | f
>     10088 |    10002 | -2062.12 | 2002-03-07 |              | f       | f
>     10096 |    10016 |  1169.75 | 2002-03-12 |              | f       | f
>     10096 |    10047 | -1169.75 | 2002-03-12 |              | f       | f
>     10096 |    10002 |  1169.75 | 2002-03-11 |              | f       | f
>     10096 |    10016 | -1169.75 | 2002-03-11 |              | f       | f
>     10098 |    10016 |   283.91 | 2002-03-12 |              | f       | f
>     10098 |    10044 |  -283.91 | 2002-03-12 |              | f       | f
>     10099 |    10016 |    137.6 | 2002-03-12 |              | f       | f
>     10099 |    10045 |   -137.6 | 2002-03-12 |              | f       | f
>     10100 |    10016 |   163.74 | 2002-03-12 |              | f       | f
>     10100 |    10046 |  -163.74 | 2002-03-12 |              | f       | f
>     10092 |    10004 |   528.75 | 2002-03-20 |              | f       | f
>     10092 |    10002 |  -528.75 | 2002-03-20 |              | f       | f
>     10101 |    10004 |  -528.75 | 2002-03-20 |              | f       | f
>     10101 |    10037 |      450 | 2002-03-20 |              | f       | f
>     10101 |    10074 |    78.75 | 2002-03-20 |              | f       | f
>
> Regards
>
> Scott
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: SELECT Query

From
Lee Harr
Date:
In article <200211011158.23530.scott.taylor@4i-dotcom.com>, Scott Taylor wrote:
> Below is the result of a query on my table. I want to only return those
> transactions that have a chart_id=10074, and if trans_id 10088 is one of
> those, I want to return all 3 rows with that trans_id.
>

SELECT * FROM acc_trans
  WHERE
    chart_id=10074
   OR
    trans_id IN (SELECT trans_id FROM acc_trans WHERE chart_id=10074)
;

I think that is what you want.
If it is slow, look at using EXISTS instead of IN.


> accs=# select * from acc_trans;
>  trans_id | chart_id |  amount  | transdate  |    source    | cleared |
> fx_transaction
> ----------+----------+----------+------------+--------------+---------+----------------
>     10088 |    10004 | -2062.12 | 2002-03-01 |              | f       | f
>     10088 |    10037 |     1755 | 2002-03-01 |              | f       | f
>     10088 |    10074 |   307.12 | 2002-03-01 |              | f       | f
>     10092 |    10004 |  -528.75 | 2002-03-12 |              | f       | f
>     10092 |    10037 |      450 | 2002-03-12 |              | f       | f
>     10092 |    10074 |    78.75 | 2002-03-12 |              | f       | f
>     10088 |    10004 |  2062.12 | 2002-03-07 |              | f       | f
>     10088 |    10002 | -2062.12 | 2002-03-07 |              | f       | f
>     10096 |    10016 |  1169.75 | 2002-03-12 |              | f       | f
>     10096 |    10047 | -1169.75 | 2002-03-12 |              | f       | f
>     10096 |    10002 |  1169.75 | 2002-03-11 |              | f       | f
>     10096 |    10016 | -1169.75 | 2002-03-11 |              | f       | f
>     10098 |    10016 |   283.91 | 2002-03-12 |              | f       | f
>     10098 |    10044 |  -283.91 | 2002-03-12 |              | f       | f
>     10099 |    10016 |    137.6 | 2002-03-12 |              | f       | f
>     10099 |    10045 |   -137.6 | 2002-03-12 |              | f       | f
>     10100 |    10016 |   163.74 | 2002-03-12 |              | f       | f
>     10100 |    10046 |  -163.74 | 2002-03-12 |              | f       | f
>     10092 |    10004 |   528.75 | 2002-03-20 |              | f       | f
>     10092 |    10002 |  -528.75 | 2002-03-20 |              | f       | f
>     10101 |    10004 |  -528.75 | 2002-03-20 |              | f       | f
>     10101 |    10037 |      450 | 2002-03-20 |              | f       | f
>     10101 |    10074 |    78.75 | 2002-03-20 |              | f       | f
>
> Regards
>
> Scott
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster