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
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
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:
Joined: Sep 10, 2004
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.