Project

General

Profile

History

Appion Home » Developers guide » MySQL Notes »

Mysql Nested Subqueries Problem » History » Revision 2

Revision 1 (Amber Herold, 03/04/2010 01:28 PM) → Revision 2/5 (Amber Herold, 03/04/2010 01:29 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.  
			 // 
			 // 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. here:http://forums.mysql.com/read.php?24,54721,54721#msg-54721