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
解决方案
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
2831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
