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.