Home » SQL & PL/SQL » SQL & PL/SQL » add the unconsildate amount to match with consolidate amount
add the unconsildate amount to match with consolidate amount [message #330920] Tue, 01 July 2008 15:08 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

i have 2 tabes,containing data something like this..


Table1

Acct_nu   loan#   invoice#   amount
1          23        1234     4
1          23        1234     5
1          23        1234     6
2          33        0987     5
2          33        0987     6

Table 2
Acct_nu   loan#    invoice#    total_amt
1          23       1234        15
1          23       1234        18

I need to select all records from table1 whose acct_nu, loan#, invoice# and total amount matches with the acct_nu, loan#, invoice#, total_amt of table 2 ... I have tried group by function, but did not work it out.I am not looking for detail ansewer.. any clue would be apprecicated
Re: add the unconsildate amount to match with consolidate amount [message #330921 is a reply to message #330920] Tue, 01 July 2008 15:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Clue:

Use the HAVING clause.
Re: add the unconsildate amount to match with consolidate amount [message #330922 is a reply to message #330921] Tue, 01 July 2008 15:27 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

You mean something like this ...

select a.* from table1 a, table2 b 
where a.acct#=b.acct# and a.loan#=b.loan# 
and a.invoice# = b.invoice# 
group by a.amount 
having sum(a.amount) = b.total_amt


Appreciate your help ...

[Updated on: Tue, 01 July 2008 15:30] by Moderator

Report message to a moderator

Re: add the unconsildate amount to match with consolidate amount [message #331161 is a reply to message #330922] Wed, 02 July 2008 07:54 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, good to see someone ask for a hint rather than the solution. If you are still having problems, please tell us, but your query is the method I was thinking about. Good effort!
Previous Topic: doubt in a group by query
Next Topic: sequence gap (merged)
Goto Forum:
  


Current Time: Sun Dec 11 04:05:50 CST 2016

Total time taken to generate the page: 0.05798 seconds