difficult query
Can somebody help me with this join, I think I need a self join on the first table and a join to the other tables I have following tables Actiontable objecttype objectid actionid sender startdate Enddate A 1 1 Marc 22/02/2000 14:10:00 25/02/2000 13:00:10 A 1 5 Erik 24/03/2000 11:00:00 26/03/2000 20:00:00 A 1 8 Sven 12/03/2000 08:02:00 25/03/2000 14:00:00 B 5 15 Jonas 14/04/2000 06:00:00 18/04/2000 19:00:00 B 5 16 Michael 14/04/2000 17:50:00 29/04/2000 16:00:00 B 5 20 Nico 22/04/2000 19:00:00 25/04/2000 13:00:00 Table for objecttype_A objecttype objectid solver A 1 Andre Table for objecttype_B objecttype objectid solver B 1 Piet For each group of objecttype+objectid I have different actions. For each action there is a sender, so the solver of the action is the sender of the next action. The solver of the last action can we find in a other table that has the name of the objecttype I need a sql to display for each action the solver This is the result So that i can calculate the difference between startdate and enddate for each solver objecttype objectid actionid sender startdate Enddate A 1 1 Erik 22/02/2000 14:10:00 25/02/2000 13:00:10 A 1 5 Sven 24/03/2000 11:00:00 26/03/2000 20:00:00 A 1 8 Andre 12/03/2000 08:02:00 25/03/2000 14:00:00 B 5 15 Mchael 14/04/2000 06:00:00 18/04/2000 19:00:00 B 5 16 Nico 14/04/2000 17:50:00 29/04/2000 16:00:00 B 5 20 Piet 22/04/2000 19:00:00 25/04/2000 13:00:00 The next action for a specific objecttype+objectid is not the actionid+1 Sometimes its more then one! Who can help me or give me a hint to create the sql. Thanks
I say forget SQL. Suck in the key tables and code the logic in ESQL/C or 4GL or C++ or whatever front-end language you are using. Let me say again, since I have not said it for tow years or so: SQL IS NOT A PROGRAMMING LANGUAGE! SQL is a user level QUERY language. Use it to QUERY the database and get information from it, code programming constructs in the front-end or if simple into Stored Procedures or if business rules type logic and complex and you have 9.xx then write a UDR in C or Java or whatever is hot this week and make your own datablade. But do not try to shoehorn your complex logic into SQL or force database structures that make little sense to support a pure SQL solution. Performance will S%&K and you will be complaining about how Informix is a bad server very quickly. Art S. Kagel Marc Notelteirs wrote: > Can somebody help me with this join, I think I need a self join on the > first table and a join to the other tables > I have following tables > Actiontable > objecttype objectid actionid sender startdate > Enddate > A 1 1 Marc 22/02/2000 14:10:00 > 25/02/2000 13:00:10 > A 1 5 Erik 24/03/2000 11:00:00 > 26/03/2000 20:00:00 > A 1 8 Sven 12/03/2000 08:02:00 > 25/03/2000 14:00:00 > B 5 15 Jonas 14/04/2000 06:00:00 > 18/04/2000 19:00:00 > B 5 16 Michael 14/04/2000 17:50:00 > 29/04/2000 16:00:00 > B 5 20 Nico 22/04/2000 19:00:00 > 25/04/2000 13:00:00 > > Table for objecttype_A > objecttype objectid solver > A 1 Andre > > Table for objecttype_B > objecttype objectid solver > B 1 Piet > > For each group of objecttype+objectid I have different actions. > For each action there is a sender, so the solver of the action is the > sender of the next action. > The solver of the last action can we find in a other table that has the > name of the objecttype > > I need a sql to display for each action the solver > This is the result > So that i can calculate the difference between startdate and enddate for each > solver > objecttype objectid actionid sender startdate > Enddate > A 1 1 Erik 22/02/2000 14:10:00 > 25/02/2000 13:00:10 > A 1 5 Sven 24/03/2000 11:00:00 > 26/03/2000 20:00:00 > A 1 8 Andre 12/03/2000 08:02:00 > 25/03/2000 14:00:00 > B 5 15 Mchael 14/04/2000 06:00:00 > 18/04/2000 19:00:00 > B 5 16 Nico 14/04/2000 17:50:00 > 29/04/2000 16:00:00 > B 5 20 Piet 22/04/2000 19:00:00 > 25/04/2000 13:00:00 > > The next action for a specific objecttype+objectid is not the actionid+1 > Sometimes its more then one! > > Who can help me or give me a hint to create the sql. > > Thanks