SELECT Query

Started by Scott Taylorover 23 years ago5 messagesgeneral

scott.taylor@4i-dotcom.com

over 23 years ago

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

Martijn van Oosterhout

kleptog@svana.org

over 23 years ago

In reply to: Scott Taylor (#1)

Re: SELECT Query

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/

Show quoted text

There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.

Richard Huxton

dev@archonet.com

over 23 years ago

In reply to: Scott Taylor (#1)

Re: SELECT Query

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

Jean-Luc Lachance

jllachan@nsd.ca

over 23 years ago

In reply to: Scott Taylor (#1)

Re: SELECT Query

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:

Show quoted text

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

Lee Harr

missive@frontiernet.net

over 23 years ago

In reply to: Scott Taylor (#1)

Re: SELECT Query

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.

Show quoted text

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