October 1, 2009 at 10:50 am
Ok, so I tried working on this for a while before I posted on here, just can't get it. When I'm wanting to do is select the SectionCallID and count the LeaderID and group it by the SectionCallID; however, I still want to be able to view the other fields on the SNP_SectionLeader table. So, I tried to do an A and B query and join them together. The code looks like this:
SELECT * FROM SNP_SectionLearner A
INNER JOIN SNP_Sections ON SNP_SectionLearner.SectionID = SNP_Sections.SectionID
INNER JOIN SNP_Courses ON SNP_Sections.CourseID = SNP_Courses.CourseID
INNER JOIN (
SELECT SectionCallID, Count(LearnerID)AS NumStudents FROM SNP_SectionLearner
INNER JOIN Users ON SNP_SectionLearner.LearnerID = Users.UserID
INNER JOIN UserRoles ON SNP_SectionLearner.LearnerID = UserRoles.UserID
INNER JOIN SNP_Sections ON SNP_SectionLearner.SectionID = SNP_Sections.SectionID
WHERE SNP_Sections.StartDate >= '06/01/2009' AND SNP_Sections.StartDate <= '06/31/2009'
AND (UserRoles.RoleID ='6' OR UserRoles.RoleID ='3' OR UserRoles.RoleID ='11')
GROUP BY SectionCallID)B
ON A.SectionID = B.SectionID
Which, to me, seems like it would work; however, not having any luck. I'm getting The multi-part identifier "SNP_SectionLearner.SectionID" could not be bound.
Any suggestions?
Thanks,
Jordon
October 1, 2009 at 11:04 am
The column you're requesting in the SELECT statement (SNP_SectionLearner.SectionID) does not exist.
Although the SNP_SectionLearner table is in your inline query, the inline query does not return that column in table "B" (as it is known to the outer query now). So, in order to combine raw data with the aggregated data you'll need to either:
a. SELECT (and then GROUP BY) SNP_SectionLearner.SectionID in table "B". That may screw up the grouping levels, though.
--OR--
b. Pull in the SNP_SectionLearner table into your outer query instead.
MJM
October 1, 2009 at 11:05 am
You should definately alias your tables better.
you could try something like this:
SELECT * FROM SNP_SectionLearner Learn
INNER JOIN SNP_Sections Sec ON Learn.SectionID = Sec.SectionID
INNER JOIN SNP_Courses Cor ON Sec.CourseID = Cor.CourseID
INNER JOIN (
SELECT SectionCallID, Count(LearnerID)AS NumStudents FROM SNP_SectionLearner Learn2
INNER JOIN Users U ON Learn2.LearnerID = U.UserID
INNER JOIN UserRoles UR ON Learn2.LearnerID = UR.UserID
INNER JOIN SNP_Sections Sec2 ON Learn2.SectionID = Learn2.SectionID
WHERE Learn2.StartDate >= '06/01/2009' AND Learn2.StartDate <= '06/31/2009'
AND (UR.RoleID ='6' OR UR.RoleID ='3' OR UR.RoleID ='11')
GROUP BY SectionCallID) B
ON Learn.SectionID = B.SectionID
But you still need to provide aliases for these columns
SELECT SectionCallID, Count(LearnerID)AS NumStudents FROM SNP_SectionLearner Learn2
Which ones do they originate from?
Incidentally, I'd recommend putting this in a CTE instead of a subquery, IE:
WITH cte AS
(
SELECT SectionCallID, Count(LearnerID)AS NumStudents FROM SNP_SectionLearner Learn
INNER JOIN Users U ON Learn.LearnerID = U.UserID
INNER JOIN UserRoles UR ON Learn.LearnerID = UR.UserID
INNER JOIN SNP_Sections Sec ON Learn.SectionID = Learn.SectionID
WHERE Learn.StartDate >= '06/01/2009' AND Learn.StartDate <= '06/31/2009'
AND UR.RoleID IN ('6', '3', '11')
GROUP BY SectionCallID
)
SELECT * FROM SNP_SectionLearner Learn
INNER JOIN SNP_Sections Sec ON Learn.SectionID = Sec.SectionID
INNER JOIN SNP_Courses Cor ON Sec.CourseID = Cor.CourseID
INNER JOIN cte ON Learn.SectionID = cte.SectionID
There might be some code flaws, but I can't do any better without table definitions
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply