0

I am getting the error :

Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.surveyid" could not be bound.

Below is the query ,what could be the issue?

 SELECT a.surveyid,* 
    FROM [360HRSurvey] where sempid IN ( 
     SELECT  a.EmpCode  
     FROM surveyusers a  
     where a.surveyid = 80 and a.EmpCode NOT IN (
      SELECT p.EmpID    
      FROM empsurveyselection p 
      WHERE p.surveyid =80)
    and empid =   ( 
      SELECT empid 
      from [360HRSurveyEmployee] 
      where surveyid = 80)
    )

4 Answers 4

0
SELECT a.surveyid,* FROM [360HRSurvey] 
inner join ( SELECT a.EmpCode,a.surveyid FROM surveyusers a where a.surveyid =     
80 and a.EmpCode NOT IN (SELECT p.EmpID FROM empsurveyselection p WHERE 
p.surveyid =80)) a 
on a.EmpCode=sempid 
where  empid = ( SELECT empid from [360HRSurveyEmployee] where surveyid = 80)
2
  • some syntax errors Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'a'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')' Jun 2, 2015 at 7:20
  • @smartestvega check now Jun 2, 2015 at 7:22
2

You need to use surveyid not a.surveyid. Because surveyusers as a declare in sub-query so you can not access a.surveyid in main query.

4
  • there is no surveyid column in 360HRSurvey hence this will give error Jun 2, 2015 at 6:49
  • @SmartestVEGA what do you want this column to return? The number 80? Jun 2, 2015 at 6:51
  • yes, i will remove that where clause of survey id in future , so i need to return all survey id appended to the main function Jun 2, 2015 at 6:53
  • for that you need to use join. Jun 2, 2015 at 7:00
2

The reason for the error was explained in Jaimin Soni's answer.

Since you want to include surveyid in the result and this is not an attribute of the [360HRSurvey] table, you could just use SELECT 80 AS surveyid, * ... for this particular query. If the surveyid is not fixed though - in a more complex query - you can join to the table that has this attribute. Assuming that this table is surveyusers and that there is a FOREIGN KEY from [360HRSurvey] (sempid) that REFERENCES surveyusers(EmpCode), the query can be rewritten:

SELECT a.surveyid, hrs.* 
FROM [360HRSurvey] AS hrs
   JOIN surveyusers AS a 
      ON a.EmpCode = hrs.sempid 
WHERE a.surveyid = 80           -- this condition can be altered or removed
  AND a.EmpCode NOT IN (
          SELECT p.EmpID    
          FROM empsurveyselection p 
          WHERE p.surveyid = a.surveyid )
  AND a.empid = (               -- unclear if it's a.empid or hrs.empid
         SELECT hrsemp.empid 
         FROM [360HRSurveyEmployee] AS hrsemp
         WHERE hrsemp.surveyid = a.surveyid ) ;
0
SELECT su.surveyid,b.* FROM surveyusers SU 
RIGHT JOIN 
(SELECT * FROM [360HRSurvey] where sempid  NOT IN (
SELECT a.EmpCode FROM surveyusers a 
where a.EmpCode IN (SELECT p.EmpID 
  FROM empsurveyselection p where deptid = 9 )
and empid IN   
( SELECT empid from [360HRSurveyEmployee] ))) B
ON su.EmpCode = b.sempid 
Order by surveyid desc

Fixed it myslelf

2
  • 2
    The where deptid = 9 was not part of the original query. Jun 2, 2015 at 8:55
  • yes i intetionaly added for a business logic change thanks Jun 4, 2015 at 11:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.