SQL question - get the first date from 2 tables

[Peter Primrose]

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]

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.

[Tural Sadiqov]

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]

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]

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:

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

[Peter Primrose]

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.