0

I'm looking into developing something in MySQL. Imagine I have some 'project' and some 'timeframes' in a MySQL database. Many 'timeframes' may belong to each 'project'. The table containing 'timeframes' has an ID linking it to each project. 'timeframes' of any sort may not overlap - and this is easy to query:

SELECT * FROM `timeframes` WHERE end > proposedstart AND start < proposedend

However - I would ideally like to return the following - a new timeframe may not be in a 'project' that is not its own (defined by the earliest 'timeframe' date belonging to that project and the latest 'timeframe' date beloning to that project) - but MAY appear between two timeframes in its own 'project'. Is there some way I could query this from the 'timeframes' table in a similar format??

Here is the output of the description

mysql> DESCRIBE projects;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| projectID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| projectName | varchar(225) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE timeframes;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| timeframeID    | int(11)      | NO   | PRI | NULL    | auto_increment |
| projectID      | int(11)      | NO   |     | NULL    |                |
| timeframeName  | varchar(225) | NO   |     | NULL    |                |
| timeframeStart | date         | NO   |     | NULL    |                |
| timeframeEnd   | date         | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Imagine I have three projects in the projects table, called Project 1, Project 2 and Project 3. Imagine that I have two timeframes associated with project 1. The first, called timeframe1, begins on 2014-01-07 and ends on 2014-03-28. The second, called timeframe2 beings on 2014-04-28 and ends on 2014-07-11. I also have two timeframes associated with project 3 - the first begins on 2015-09-7 and ends on 2015-12-11. The second begins on 2016-01-06 and ends on 2016-03-18. Now - imagine I want to add a timeframe that is related to project 2. It may not be any dates between 2014-01-07 and 2014-07-11 or any dates between 2015-09-07 and 2016-03-18. So even though a timeframe for project 2 could fit in between the two timeframes for project 1 or 3, I do not want this to happen. Acceptable values for any timeframe in project 2 for instance would be 2013-12-12 to 2014-01-06, 2014-07-12 to 2015-09-06 or some narrower range. I would not want timeframes such as 2014-04-01 to 2014-04-27 to be valid for project 2. Those dates, may however, be valid for project 1 only.

etoipi
  • 57
  • 4

1 Answers1

0

Sorry, my mistake. Forget what I wrote previously.

Edited Answer:

Sorry untested!

SELECT `projectId`
     , MIN(`timeframeStart`) AS `start`
     , MAX(`timeframeEnd`) AS `end`
  FROM `timeframes`
  WHERE `projectId` != @proposedprojectid
  GROUP BY `projectId`  
  HAVING @proposedstart <= `projectMinMax`.`end`
     AND @proposedend >= `projectMinMax`.`start`

Yes I know HAVING is evil, but I see now other way.

Frank Olschewski
  • 500
  • 4
  • 9