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
[email protected]


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<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]