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/win32?unsub=gcdmw-win32 [at] m.gmane.org
