MySQL Correlated sub queries

问题:

Can any one help me i have problem with Correlated nested sub queries i.e., when i am trying to use grand parent column in nested sub query then i got this error

Error Code: 1054 Unknown column 'scu.iUserId' in 'where clause'

Query:

    SELECT 
      scu.iUserId,
      (SELECT 
        SUM(
          sbs.`iNoPointsBeginning` + 
          (SELECT 
            COALESCE(SUM(BehaviorPts), 0) AS StudentPts 
          FROM
            (SELECT 
              (
                COUNT(sbis.iIncidentSubmissionId) * sbi.iPointValue
              ) AS BehaviorPts 
            FROM
              scn_behavior_incident_submission sbis 
              JOIN scn_behavior_incident_actors sbia 
                ON sbia.iIncidentSubmissionId = sbis.iIncidentSubmissionId 
              LEFT JOIN scn_behavior_incidents sbi 
                ON sbi.iIncidentId = sbis.iBehaviorIncidentId 
            WHERE sbia.iUserId = scu.iUserId
              AND sbia.eActorType  'Witness' 
              AND sbis.iSchoolId = '875' 
            GROUP BY sbis.iBehaviorIncidentId) AS BehaviorTotal)
        ) AS stu_pt 
      FROM
        scn_behavior_settings sbs 
      WHERE sbs.`iSchoolId` = '875') 

    FROM
      scn_sections_members AS scm 
      INNER JOIN scn_users AS scu 
        ON scu.iUserId = scm.iStudentId 

回答1:

The two levels of nesting cause this error. The very internal subquery does not "know" scu which is defined in the external query.

Try to rewrite it without inline subqueries. Not sure if this is the proper way but you'll get the idea. (the sbs table seems unrelated to the other ones so I made that a CROSS JOIN. Edit appropriately if there is a relationship):

SELECT 
    scu.iUserId,
    sbs.iNoPointsBeginning 
    + COUNT(sbis.iIncidentSubmissionId) * COALESCE(sbi.iPointValue, 0)
      AS stu_pt 
FROM
    ( SELECT 
          SUM(sbs.iNoPointsBeginning) AS iNoPointsBeginning
      FROM 
          scn_behavior_settings sbs 
      WHERE sbs.iSchoolId = '97'
    ) AS sbs 
  CROSS JOIN
      scn_sections_members AS scm 
  INNER JOIN scn_users AS scu 
    ON scu.iUserId = scm.iStudentId 
  LEFT JOIN
          scn_behavior_incident_submission sbis 
        JOIN scn_behavior_incident_actors sbia 
          ON  sbia.iIncidentSubmissionId = sbis.iIncidentSubmissionId 
          AND sbia.eActorType = 'Witness' 
          AND sbis.iSchoolId = '97' 
        LEFT JOIN scn_behavior_incidents sbi 
          ON sbi.iIncidentId = sbis.iBehaviorIncidentId 
    ON sbia.iUserId = scu.iUserId
GROUP BY scu.iUserId
       , sbis.iBehaviorIncidentId

回答2:

If you (temporarily) remove that messy tangle of sub-queries, you'll see that you are saying:

SELECT scu.iUserId 
FROM scn_sections_members AS scm 
INNER JOIN scn_users AS scu 
ON scu.iUserId = scm.iStudentId

For MySQL to tell you that the column is unknown, it must mean that the column doesn't exist. What is the definition of scn_users? Put a iUserId column in that table and then at least that part of your problem will be over.