Home » SQL & PL/SQL » SQL & PL/SQL » Query output Exist / not exist
Query output Exist / not exist [message #199768] Thu, 26 October 2006 02:57 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I have retriving set of data by query in this way.
party		Account		Counterparty 	Currencyentered_dr	entered_cr	
260C		780000		431C		SGD	254		0		
700C		780000		431C		GBP	0		200		
700C		780000		260C		EUR	254		0		
431C		780000		700C		EUR	0		200	
700C		680000		431C		EUR	200		0	
260C		680000		700C		SGD	0		254		
431C		680000		700C		EUR	154		0		
260C		780000		700C		SGD	124		0	
700C		680000		260C		SGD	0		124	

I am trying to make a use of Exist and not Exist to get the pair of records.

I need to get the result like
260C		780000		700C		SGD	124		0	
700C		680000		260C		SGD	0		124	
431C		780000		700C		EUR	0		200	
700C		680000		431C		EUR	200		0	

ie my codition for getting second output should be like:
party column should be match with counter party and account of 680000 or 780000 should be adjectly opposite and amount should be counter part.

is there any way to get this desired result, by sql query.
any input..
Re: Query output Exist / not exist [message #199793 is a reply to message #199768] Thu, 26 October 2006 03:57 Go to previous message
romi
Messages: 67
Registered: October 2006
Member


try it.

select distinct(a.party),a.account,a.counterparty,a.currency,a.entered_dr,a.entered_cr

from a3 a,a3 b

where a.party=b.counterparty
AND a.currency=b.currency
AND a.entered_dr=b.entered_cr

order by currency desc;
Previous Topic: Changing Fieldtypes
Next Topic: Need Query Result
Goto Forum:
  


Current Time: Sun Dec 04 00:44:45 CST 2016

Total time taken to generate the page: 0.13898 seconds