grants.grantid grants.agency 1 NIH-F 2 NIH-F 3 NIH-F
dates.dateid dates.grantid dates.yearday 1 1 31 2 1 60 3 2 45 4 2 200
They are related by grantid. Note that grantid# 3 from the grants table does not have an entry in the dates table as there are not yet dates for that particular grant.
Here is my SELECT statement:
SELECT grants.grantid, MIN(dates.yearday) AS min_daynum FROM grants, dates WHERE grants.agency = "NIH-F" OR (grants.agency = "NIH-F" AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY min_daynum;
The select doesn't work for grabbing the MIN(dates.yearday). It only gets the first one: '31' and each row displays that yearday.
grantid min_daynum 1 31 2 31 3 31
Correct listing (if I knew what I was doing) would be:
grantid min_daynum 3 1 31 2 45
Removing the OR clause on the WHERE fixes the problem but then grantid# 3 from grants is not selected (because it's not in the dates table).
I can see one fix is to list each and every grant in the dates table. Is there another way to write this so that it will work?
Thanks. -- Peter Fleck Webmaster | University of Minnesota Cancer Center Dinnaken Office Bldg. 925 Delaware St. SE Minneapolis, MN 55414 612-625-8668 | [EMAIL PROTECTED] | www.cancer.umn.edu Campus Mail: MMC 806
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
