wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL question - get the first date from 2 tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL question - get the first date from 2 tables" Watch "SQL question - get the first date from 2 tables" New topic
Author

SQL question - get the first date from 2 tables

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
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
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30598
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Tural Sadiqov
Greenhorn

Joined: Oct 22, 2005
Posts: 2
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

Joined: Sep 10, 2004
Posts: 755
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
Marshal

Joined: May 26, 2003
Posts: 30598
    
154

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

Joined: Sep 10, 2004
Posts: 755
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.
 
GeeCON Prague 2014
 
subject: SQL question - get the first date from 2 tables