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
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@m.gmane.org

Re: Best method for relating by date

Personally, I would add a unique key to your assignment table, then
reference that id in your effort table. From there you have a simple
join and no confusion as to which effort belongs to which assignment.

Randy Clamons
Systems Programming
randy@novaspace.com


Joelle Tegwen wrote:
> 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 >
> 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@m.gmane.org