• 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
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

SQL question - get the first date from 2 tables

 
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

Say I have 3 tables:

Table_A: ID, Name, DOB
Table_B: ID, cruiseDate
Table_C: ID, flightDate

Q: how can I get set of:
ID, Name, DOB and the first date of either cruiseDate or flightDate?

eg
Table_A: 107, Gil, 10-Oct-68

Table_B: 107, 22-Nov-05
107, 23-Nov-05

Table_C: 107, 14-Dec-05
107, 18-Dec-05
107, 19-Dec-05

The result should come as:
107, Gil, 10-Oct-68, 22-Nov-05

* the first date (the closer date from today) is 22-Nov-05
* there might be more than one cruise or more than one flight and there might none.

Thanks for any tip.
peter
 
author & internet detective
Posts: 42165
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Peter,
If you are using JDBC, the easiest way is to get the earliest cruise date and flight date for each user. Then compare in Java which is earlier.

Make sure to use an outer join rather than a regular join to accomodate users who have never gone on a cruise/flight.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This about database management
always do data manupulating only on the database side not on the applicatin side
applications must only make requests to databases (that is a good DB desighn)
I'm not sure I understood your problem but I'll try to help

You have 3 tables

1. Table holding data about people flying ( customers)

CUSTOMER( ID, Name, DOB )

2. Table holding data about flights
FLIGHT( custID, FlightDate )


3. Table holding data about cruises
CRUISE( custID, CruiseDate )

and u want something like

ID, Name, most recent of cruise and flight dates

but u can only get it as ( ID ,Name, FlightDate, CruiseDate)


1. You can create a view or query (let's call it AnyAction or D) in witch there are two fields ID and date without showing if it is a FlightDate or CruiseDate (use union) like:

CREATE VIEW AnyAction( ID , ActionDate ) AS

SELECT ID, FlightDate
FROM Flight

UNION

SELECT ID, CruiseDate
FROM Cruise;


from this query or view ( AnyAction ) you can select the most recent date:

SELECT a.ID, a.Name, a.DOB, MAX(d.ActionDate)
FROM Customer a, AnyAction d
WHERE a.ID=d.ID
GROUP BY a.ID


2. You can link any function FMD(date1, date2) from a *.dll file written in c++,Delhi or � and write smth like

SELECT a.ID, a.Name, FMD( b.FlightDate, c.CruiseDate)
FROM...
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you Tural, this has been a great help.

Q: what about null values? ie, what if both dates are null but i still want to see that other values?

thanx
 
Jeanne Boyarsky
author & internet detective
Posts: 42165
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tural,
Welcome to JavaRanch!

I agree that data manipulation should be done on the database side. However, business logic could be done on either the database or application side (depending on the application architecture.) In this case, I think it could be considered business logic or data manipulation.

I like your solution though. It's much cleaner than explictly writing the subqueries.

Peter,
You still need the outer join to deal with nulls. Syntax tends to vary somewhat by database. This is an example for Oracle:
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you Jeanne
I solved it. In this specific case Tural was right. It makes more sense to manipulate the data in the db and not on the application side (again, in this specific case)
Well, I learned 2 things today. thank you all.
 
reply
    Bookmark Topic Watch Topic
  • New Topic