SQL statement with Multiple SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL statement with Multiple SELECT

Author

Topic

marsulein
Starting Member

11 Posts

Posted - 2003-12-09 : 23:28:19

I am working in VB6 with MS Access 2002 (SP2). I encountered problem while trying to run through this code:

SELECT X.CompetencyID, Sum(X.AvgResult1) AS AvgResult,Sum(X.N_Rating) AS SN_Rating, Sum(X.N_KB) AS SN_KB, SN_RATING/SN_KB AS N_Rater 
              FROM (SELECT B.FKCompetency, Avg(R.Result) AS AvgResult1, Count(A.RaterLoginID) AS N_Rating, 0 as N_KB 
                    FROM KeyBehaviour AS B RIGHT JOIN (tblAssignment AS A INNER JOIN 
                        (tblResultBehaviour AS R INNER JOIN tblRatingTask AS T ON R.RatingTaskID = T.RatingTaskID) 
                        ON A.AssignmentID = R.AssignmentID) ON B.PKKeyBehaviour = R.KeyBehaviourID 
                    WHERE A.SurveyID = 258 AND A.TargetLoginID = 'maruli' AND A.RaterStatus IN (1,2) AND 
                           T.RatingCode = 'CP' And A.RTRelation <>2 
                    GROUP BY B.FKCompetency 
                     Union 
                    SELECT T.CompetencyID,0 AS AvgResult1,0 as N_Rating, Count(T.KeyBehaviourID) AS N_KB 
                    FROM 
                        (SELECT KB.FKCompetency,R.KeyBehaviourID 
                         FROM KeyBehaviour AS KB RIGHT JOIN (tblAssignment AS A INNER JOIN (tblResultBehaviour AS R INNER JOIN 
                              tblRatingTask AS T ON R.RatingTaskID = T.RatingTaskID) ON A.AssignmentID = R.AssignmentID) ON KB.PKKeyBehaviour = R.KeyBehaviourID 
                         WHERE A.SurveyID = 258 AND A.TargetLoginID = 'maruli' AND A.RaterStatus IN (1,2) AND 
                              T.RatingCode = 'CP' And A.RTRelation <>2 
                         GROUP BY KB.FKCompetency, R.KeyBehaviourID) AS T 
                    GROUP BY T.CompetencyID ) AS X 
                GROUP BY X.CompetencyID;

All the tables name are perfectly fine, as well as all the fields and values. The error message i got when i try to compile is:
'The specified field 'T.CompetencyID' could refer to one or more than one table listed in the FROM clause of your SQL statement'

Thanx in advance!!!

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-10 : 03:36:37

You use the "T" alias in three different places. You alias both a table in a subquery and a subquery as "T". The error message you got tells you exactly what the problem is: "T.CompetencyID" could refer to more than one table.

I don't like aliases. Use the table names if possible. Use an alias when you have to (a subquery with an auto-reference is an example). And aliases don't have to be just one character-- I see one character aliases all the time and wonder where that idea came from. Give your reader a clue as to what the table is for. Call the alias "client" or "student", not "c" or "s".

--
Timothy Chen Allen
email me if you have a job in New Orleans for me
[url]http://www.timallen.org[/url]

marsulein
Starting Member

11 Posts

Posted - 2003-12-11 : 20:54:27

Thanx for your suggestions Tim. I was assigned to debug this program written by other. I changed the codes slightly now. The error message is gone now. However, when i execute in MSAccess, it prompt me to enter the value for T.CompetencyID & X.CompetencyID.

SELECT X.CompetencyID, Sum(X.AvgResult1) AS AvgResult,Sum(X.N_Rating) AS SN_Rating, Sum(X.N_KB) AS SN_KB, SN_RATING/SN_KB AS N_Rater 
    
FROM 
	(SELECT B.FKCompetency, Avg(R.Result) AS AvgResult1, Count(A.RaterLoginID) AS N_Rating, 0 as N_KB 
	FROM KeyBehaviour AS B, tblAssignment AS A, tblResultBehaviour AS R, tblRatingTask AS T 
	WHERE R.RatingTaskID = T.RatingTaskID AND A.AssignmentID = R.AssignmentID 
            	AND B.PKKeyBehaviour = R.KeyBehaviourID 
             	AND A.SurveyID = 258 AND A.TargetLoginID = 'maruli' 
              	AND A.RaterStatus IN (1,2) AND T.RatingCode = 'CP' 
              	AND A.RTRelation <>2 
	GROUP BY B.FKCompetency 

	UNION

	SELECT T.CompetencyID,0 AS AvgResult1,0 as N_Rating, Count(T.KeyBehaviourID) AS N_KB 
	FROM 
		(SELECT KB.FKCompetency,R.KeyBehaviourID 
		FROM KeyBehaviour AS KB, tblAssignment AS A, tblResultBehaviour AS R, tblRatingTask AS RatingTask 
		WHERE R.RatingTaskID = RatingTask.RatingTaskID AND A.AssignmentID = R.AssignmentID AND KB.PKKeyBehaviour = R.KeyBehaviourID 
              		AND A.SurveyID = 258 AND A.TargetLoginID = 'maruli' 
              		AND A.RaterStatus IN (1,2) AND RatingTask.RatingCode = 'CP' 
              		AND A.RTRelation <>2 
		GROUP BY KB.FKCompetency, R.KeyBehaviourID) AS T 
	
	GROUP BY T.CompetencyID) AS X 

GROUP BY X.CompetencyID;

Any suggestions are welcome, and thanx in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources