Join Error

[Jesse]

I'm running the following query:

SELECT S.Name As School,S.State,
  CASE WHEN Unfinished.Cnt IS NULL THEN 'Yes' ELSE 'bNo/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]

join error

[Anthony W.]

Hi,

I think this is an error, anyways i'm trying to join 6 tables together.
and when i do EXPLAIN SELECT i get the message in comments

Impossible WHERE noticed after reading const tables
this is my statment basically
SELECT table1.id
FROM table1,table2,table3,table4,table5,place
WHERE
table1.col1 BETWEEN 150 AND 196 AND
table1.col2 BETWEEN 50 AND 77 AND
table1.col3 IN ( 2 ) AND
table2.col1 IN ( 3 ) AND
table2.col2 IN ( 14 ) AND
table2.col3 IN ( 3 ) AND
table3.col1 IN ( 1 ) AND
table3.col2 IN ( 1 ) AND
table3.col3 IN ( 04 ) AND
table4.col1 IN ( 3 ) AND
table4.col2 IN ( 2 ) AND
table4.col3 IN ( 1 ) AND
 table5.col1 IN ( 1 ) AND
table5.col2 IN ( 1 ) AND
table5.col3 BETWEEN 18 AND 41 AND
place.longitude BETWEEN - 100.310005083878 AND - 91.6899949161224 AND
place.latitude BETWEEN 66 AND 29.385577346152 AND
 3963 * ( acos( sin( radians( place.latitude ) ) * sin( radians(
33.00 ) ) + cos( radians( place.latitude ) ) * cos( 33.00 )
* cos( radians( - 96.00 - place.longitude ) ) ) ) = 250 AND
table1.id = table2.id AND table2.id = table3.id AND table3.id = table4.id
AND table4.id = table5.id AND table5.id = place.id

Any help is appreciated.
Anthony



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: join error

[Anthony W.]

Hi,

I saw the error. 
For interested it is in the place.latitude BETWEEN  section

Anthony



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]