posted 18 years ago
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