I have the data and I have the result, I just don't have the query.

Hi there,

   I'm scratching my head over the following problem.

   Suppose I have a table of events, which specifies event type, start 
and end times, like so:

CREATE TABLE events (
event_type INT NOT NULL REFERENCES types (event_type),
dt_start DATETIME NOT NULL,
dt_end DATETIME, -- NULL means event goes on forever
KEY (event_type) -- event_type is not unique
);

Example:
+------------+------------+------------+
| event_type | dt_start   | dt_end     |
+------------+------------+------------+
|         20 | 2002-06-24 | 2005-06-24 |
|         24 | 2001-02-12 | NULL       |
|         25 | 2002-04-01 | 2005-04-01 |
|         25 | 2003-05-24 | 2006-05-24 |
|         26 | 2003-05-24 | 2004-05-24 |
|         31 | 2003-05-24 | NULL       |
|         40 | 2003-05-24 | NULL       |
|         42 | 2001-08-30 | 2006-08-30 |
+------------+------------+------------+

   Then I have a table of "conditions", a condition being defined as a 
number of events occurring simultaneously:

CREATE TABLE conditions (
condition_id INT NOT NULL,
event_type INT NOT NULL REFERENCES types (event_type),
PRIMARY KEY (condition_id, event_type)
);

Example:
+--------------+------------+
| condition_id | event_type |
+--------------+------------+
|            6 |         20 |
|            6 |         26 |
|            6 |         42 |
+--------------+------------+

   From that information, I should be able to determine the *times* 
during which a condition is present.  So, in the example above, I should 
be able to say that condition 6 is present between 2003-05-24 and 
2004-05-24, as those are the dates during which all of events 20, 26, 
and 42 are occurring.  One must keep in mind that conditions could be 
present at various discontinuous points in time, and that two or more 
events of the same type could be present simultaneously (e.g., 25 above).

   Now, I don't seem to be able to come up with an adequate SQL 
statement that will give me the above information.  I was trying to get 
something such as:

+--------------+------------+------------+
| condition_id | dt_start   | dt_end     |
+--------------+------------+------------+
|            6 | 2003-05-24 | 2004-05-24 |
+--------------+------------+------------+

   I would very much appreciate any assistance that you could offer on 
this one.  TIA.

Regards,
Diego Berge.
--
e-mail address invalid, please reply to newsgroups.