SQL question

Hi, all. Actually, I am working with Sybase AS IQ 12.6, but hope that your SQL experience would be helpful Situation - I have several tables, containing date fields: table1(start_date, end _date, id1, id2) table2(start_date, end _date, id1, id2) table3(start_date, end _date, id1, id2) table4(start_date, end _date, id1, id2) table5(start_date, end _date, id1, id2) I need to get minimal start_date value from all of those tables and maximal end _date value from all tables. Result would be longest data period, described by those two fields across all tables. It would be great, if I could solve at least this. Final task is to get combination in such hierarchy : id1 ->id2 -> period, like having selected longest time periods for each id2 and id1 combination (id1 and id2 is in one-to-many relationship) Any helpful ideas? Thanks!

On 28 Dec 2005 03:03:22 -0800, gucis <andris_sh@yahoo.com> wrote > Actually, I am working with Sybase AS IQ 12.6, but hope that your SQL > experience would be helpful > > Situation - I have several tables, containing date fields: > > table1(start_date, end _date, id1, id2) > table2(start_date, end _date, id1, id2) > table3(start_date, end _date, id1, id2) > table4(start_date, end _date, id1, id2) > table5(start_date, end _date, id1, id2) > > I need to get minimal start_date value from all of those tables and > maximal end _date value > from all tables. Result would be longest data period, described by > those two fields across all tables. In Informix terms, you can run separate MIN and MAX operations on each table separately into a temporary table, and then run MIN and MAX on the temporary table to get the final answer: SELECT MIN(start_date) AS min_start_date, MAX(end_date) AS max_end_date FROM table1 INTO TEMP min_max; INSERT INTO min_max SELECT MIN(start_date) AS min_start_date, MAX(end_date) AS max_end_date FROM table2;... SELECT MIN(min_start_date) AS answer_part1, MAX(end_date) AS answer_part2 FROM min_max; You might be able to do something fancy without temporary tables, but it won't be much less verbose than the above. Whether that translates usefully into Sybase is a separate discussion. > It would be great, if I could solve at least this. > > > Final task is to get combination in such hierarchy : id1 ->id2 -> > period, like having selected longest time periods for each id2 and id1 > combination (id1 and id2 is in one-to-many relationship) That's a fair bit trickier. Part of the answer is to include id1 and id2 in the select lists, and to group the results by id1 and id2. However, we need to understand what you mean by 'longest time periods'. Suppose table1 contains two rows: id1 = 1, id2 = 1, start_date = 1066-10-14, end_date = 1066-12-25 (Battle of Hastings to Coronation of William I "The Conqueror") id1 = 1, id2 = 1, start_date = 2005-01-01, end_date = 2005-12-31 Is the longest time period the longest interval (here 364 days), or is it the gap between the oldest date (here 1066-10-14) and the newest (2005-12-31)? What if the first row is in table1 and the second in table2? > Any helpful ideas? In IDS, use ALTER FRAGMENT to combine the existing separate tables into a single table, probably partitioned (fragmented) by date ranges. It makes the query processing much, much easier - regardless of which answers you give to my questions about the meaning of longest interval. SQL makes it hard to deal with queries where the tables names are variables - you are treading into dynamic SQL, and you need to write the same query (or query fragment) over and over for each separate table. Look sceptically at any design where you five tables such as those you showed - it is usually an indication that there's a problem in the database design. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/