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?