• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Joining Two Tables

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic