Best method for relating by date

I've got a table Effort:
DROP TABLE IF EXISTS `projecttracking`.`effort`;
CREATE TABLE  `projecttracking`.`effort` (
  `x500` varchar(10) NOT NULL default '',
  `projectID` tinyint(3) unsigned NOT NULL default '0',
  `dateWorked` date NOT NULL default '0000-00-00',
  `regEffort` float NOT NULL default '0',
  `otEffort` float NOT NULL default '0',
  PRIMARY KEY  (`x500`,`projectID`,`dateWorked`),
  KEY `proj` (`projectID`),
  CONSTRAINT `user` FOREIGN KEY (`x500`) REFERENCES `users` (`x500`) ON 
UPDATE CASCADE,
  CONSTRAINT `proj` FOREIGN KEY (`projectID`) REFERENCES `project` 
(`projectID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and a table Assignment:
DROP TABLE IF EXISTS `projecttracking`.`assignment`;
CREATE TABLE  `projecttracking`.`assignment` (
  `x500` char(10) NOT NULL default '',
  `projectID` tinyint(3) unsigned NOT NULL default '0',
  `startDate` date NOT NULL default '0000-00-00',
  `percentEffort` float NOT NULL default '0',
  PRIMARY KEY  (`x500`,`projectID`,`startDate`),
  KEY `projectID` (`projectID`),
  CONSTRAINT `projectID` FOREIGN KEY (`projectID`) REFERENCES `project` 
(`projectID`) ON UPDATE CASCADE,
  CONSTRAINT `x500` FOREIGN KEY (`x500`) REFERENCES `users` (`x500`) ON 
UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


And I want to find the Efforts related to a given Assignment (Ax).

To do that I need all of the Efforts WHERE dateWorked>=Ax.startDate
But if there is an assignment after Ax (call it Ay) I also need AND 
dateWorked<Ay.startDate

What is the best way to figure out Ay.startDate (if there is one at all)?

Is this "a great opportunity to learn stored Functions? Do I write 
another query like SELECT startDate FROM Assignment WHERE 
startDate>[Ax.startDate] ORDER BY startDate LIMIT 1?

Is there a better way that I'm not seeing?

Thanks
Joelle

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]