• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Joining Two Tables

 
Rajesh jayakumar
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a problem in getting results joining 2 tables.I will explain the situation.I have two table INCIDENT and PAYMENT_DETAILS.incident_no is the primary key for table incident and foriegn key for payment_details.For each incident there will be an entry in incident table and for each payments for that incident there will be multiple records in the payment table.
so one to many mapping.so each incident which passes through different payment status like open,partially paid and paid .Now the problem i have a search which will give the incident and last payment details .So i made a join and got all the records .but i want only the last recorded payment only (by payment_date) for each record.How can i do it in a single query.

Query i used is given below

SELECT INC.VEHICLE_REG_NO,
INC.INCIDENT_REF_NO, INC.VIOLATION_REF_NO,INC.TOLL_ROAD,"
To_Char(INC.INCIDENT_TIMESTAMP, 'dd/mm/yy') AS INCIDENT_DTATE,
To_Char(INC.INCIDENT_TIMESTAMP,'hh24:mi') AS INCIDENT_TIME,"
INC.INCIDENT_STATUS,
PMT.PAYMENT_STATUS, PMT.PAYMENT_MODE,
To_Char(PMT.PAYMENT_DATE,'dd/mm/yy') AS PAYMENT_DATE,
PMT.PAY_AMOUNT,INC.INCIDENT_NO FROM
INCIDENT INC,PAYMENT_DETAILS PMT"
WHERE
INC.INCIDENT_NO = PMT.INCIDENT_NO(+)

Thanks in Advance
 
vu lee
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One way to do this is to order the result by payment date and pick the first record of the result. If you want just a single record shipping back from database server to your app server, limit the number of record return to 1. I don't know what database you use. If it is oracle, the sql stmt looks like this.
select ....
from ....
where ...
and row_num = 1
order by payment_date
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic