Project

General

Profile

History

Appion Home » Developers guide » MySQL Notes »

Mysql Nested Subqueries Problem » History » Revision 4

Revision 3 (Amber Herold, 03/04/2010 01:38 PM) → Revision 4/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.  
			
 <pre> 
 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."  
     ) 
 ); 
 </pre> 
			
 <pre> 
 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; 
 </pre> 


 SessionNames.SessionName  


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