Question About Selecting Only Some Records in a Join Query

[Kaydell On His PC <pc...@kaydell.com>]

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 

Re: Question About Selecting Only Some Records in a Join Query

[Donald McLean <dm...@gmail.com>]

It seems to me that you could probably use a subquery to get the last date
of service. It's a bit more complicated with separate fields for the date
parts. You might even have to use nested subqueries. Get the largest service
year, use that as a key and get the largest month for records in that
service year and then get the largetst service day in that service
year/month and then use that to select records from the table.