db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kaydell On His PC" ...@kaydell.com>
Subject Question About Selecting Only Some Records in a Join Query
Date Fri, 19 Jun 2009 21:46:34 GMT
Dear Derby Users:

I am using Derby for a program and I have successfully done the following query, which is
close, but needs to be refined 
some:

  // select the data to appear in the customers' search window
  String sql =
   "SELECT " +
   "CUSTOMERS.RECORD_ID, " +
   "CUSTOMER_HIST_LINES.SERVICE_YEAR AS YYYY, " +
   "CUSTOMER_HIST_LINES.SERVICE_MONTH AS MM, " +
   "CUSTOMER_HIST_LINES.SERVICE_DAY AS DD, " +
   "CUSTOMERS.HOUSE, " +
   "CUSTOMERS.STREET, " +
   "CUSTOMERS.CITY_NAME, " +
   "CUSTOMERS.LAST_NAME_1, " +
   "CUSTOMERS.FIRST_NAME_1, " +
   "CUSTOMERS.FIRST_NAME_2, " +
   "CUSTOMERS.PHONE_1, " +
   "CUSTOMERS.SPRINKLERS, " +
   "CUSTOMERS.FACES, " +
   "CUSTOMERS.PRICE, " +
   "CUSTOMERS.FREQUENCY, " +
   "CUSTOMERS.COMMENTS, " +
   "CUSTOMERS.PHONE_2, " +
   "CUSTOMERS.EMAIL, " +
   "CUSTOMERS.ZIP_CODE, " +
   "CUSTOMERS.LAST_NAME_2, " +
   "CUSTOMERS.SUBDIVISION, " +
   "CUSTOMERS.STATE " +
   "FROM CUSTOMERS " +
   "LEFT JOIN CUSTOMER_HIST_LINES " +
   "ON CUSTOMERS.RECORD_ID = CUSTOMER_HIST_LINES.CUSTOMER_RECORD_ID ";

I have two database tables that I'm trying to join, CUSTOMERS and CUSTOMER_HIST_LINES (the
second table is the customers 
work-order history).  The above query does join the customers table with the customers work-order
history, but it gives 
me every date of service.  What I need is a query that will only yield records with the last
work-order by date.  If the 
customer has no work-order history, then I need only the customer record with null fields
for the work-order history.

Will someone help me with this query so that it yields only tuples with the last date-of-service
and not every date of 
service?

Thanks for reading my question.

Kaydell 


Mime
View raw message