Appion

Appion Home » Developers guide » MySQL Notes »

Mysql Nested Subqueries Problem » History » Revision 3

Revision 2 (Amber Herold, 03/04/2010 01:29 PM) → Revision 3/5 (Amber Herold, 03/04/2010 01:38 PM)

h1. Mysql Nested Subqueries Problem 

 

			 // This is the query that I wanted to do because it is easy to understand (= easier to maintain). 
 
			 // Unfortunatly there is a bug in mysql (http://bugs.mysql.com/bug.php?id=10312) which makes this  
  
			 // so slow that no one has seen it complete. The subsequent query is a bit more difficult to follow 
 
			 // but gets around this problem.  
			
 SELECT  
			 // 
			 // NOTE: shareexperiments.experimentId and projectexperiments.projectexperimentId match but are not  
			 // the same as DB_LEGINON.SessionData.DEF_id. For finding the ProjectIds that correspond to a session, 
			 // we need to use the SessionData id. 
			
 // 			 $sharedProjectsQuery = "SELECT ".DB_PROJECT.".projectexperiments.projectId  
 " 
 //  				 ." FROM ".DB_PROJECT.".projectexperiments  
 " 
 // 				 ." WHERE ".DB_PROJECT.".projectexperiments.name IN  
 " 
 // 				 ." (  
     " 
 // 					 ." SELECT ".DB_LEGINON.".SessionData.name  
     " 
 // 					 ." FROM ".DB_LEGINON.".SessionData  
     " 
 // 					 ." WHERE ".DB_LEGINON.".SessionData.`DEF_id` IN  
     " 
 // 					 ." (  
         " 
 // 						 ." SELECT ".DB_PROJECT.".shareexperiments.`REF|leginondata|SessionData|experiment`  
         " 
 // 						 ." FROM ".DB_PROJECT.".shareexperiments  
         " 
 // 						 ." WHERE ".DB_PROJECT.".shareexperiments.`REF|leginondata|UserData|user` = ".$userId."  
     " 
 // 					 ." ) " 
 ); 
			
 // 				 ." )"; 
			
			 $sharedProjectQuery = " SELECT amberproject.projectexperiments.projectId  
 " 
				 ." FROM amberproject.projectexperiments  
 "  
				 ." INNER JOIN  
 ( 
     " 
					 ." SELECT amberdbemdata.SessionData.name AS SessionName  
     " 
					 ." FROM amberdbemdata.SessionData  
     " 
					 ." INNER JOIN  
     (  		
         " 					
						 ." SELECT amberproject.shareexperiments.`REF|leginondata|SessionData|experiment` AS SessionId  
         " 
						 ." FROM amberproject.shareexperiments  
         " 
						 ." WHERE amberproject.shareexperiments.`REF|leginondata|UserData|user` = ".$userId."  
     " 
					 ." ) AS SessionIds  
     ON amberdbemdata.SessionData.`DEF_id` = SessionIds.SessionId  
 " 
				 ." ) AS SessionNames  
 ON amberproject.projectexperiments.name = SessionNames.SessionName  


 "; 


 See another example "here":http://forums.mysql.com/read.php?24,54721,54721#msg-54721.