I'm running the following query:
SELECT S.Name As School,S.State,
CASE WHEN Unfinished.Cnt IS NULL THEN 'Yes' ELSE '<b>No</b>' END As
AllSubmitted,
COALESCE(Part.Cnt,0) As StudentCount,
COALESCE(Adv.Cnt,0) As AdvisorCount
FROM InvHead I
JOIN Schools S On S.ID=I.ChapterID
LEFT OUTER JOIN (SELECT I2.ChapterID,Count(*) As Cnt FROM InvHead I2
WHERE I2.RegFinishedDate IS NULL
GROUP BY I2.ChapterID) AS Unfinished ON Unfinished.ChapterID=S.ID
LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt
FROM Participants P JOIN StatusCodes S ON S.Code=P.Status
WHERE S.PersonType='S'
GROUP BY ChapterID) AS Part ON Part.ChapterID=S.ID
LEFT OUTER JOIN (SELECT ChapterID,Count(*) As Cnt
FROM Participants P JOIN StatusCodes S ON S.Code=P.Status
WHERE S.PersonType='A'
GROUP BY ChapterID) AS Adv ON Adv.ChapterID=S.ID
GROUP BY S.State, S.Name
ORDER BY S.State,S.Name
When I run this through my asp.net application, I get the error, "#42000The
SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use
SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay"
When I refresh, ir just backup and try again, it runs fine. I've tried
setting "SQL_BIG_SELECTS=1" in my.ini, but MySQL won't re-start in services
when I do that. MAX_JOIN_SIZE is set to 4294967295. That seems pretty huge
to me, But, I guess I can change it, but what do I change it to? Or, is
there a better way to do this by changing my query?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
