sqlsql-serversql-server-2008sql-in

IN query in SQL Server


Is this structure with IN is right or wrong this gives me error

SELECT TblProjectResources.UserId 
FROM TblTasks,TblProjectResources 
WHERE TblTasks.ProjectId=TblProjectResources.ProjectId 
AND TblTasks.TaskId=@TaskId AND TblProjectResources.IsRemoved=0 
AND TblProjectResources.UserId IN(
     (SELECT UserId 
      FROM TblProjectResources 
      WHERE IsRemoved=0 
      AND ProjectApproval=1 
      AND ProjectId=@ProjectId) 
     or 
     (SELECT TblAssignments.AssigneeId 
      FROM TblTasks,TblAssignments 
      WHERE TblTasks.TaskId=TblAssignments.AssignmentEntityId 
      AND TblAssignments.AssignmentEntity='Task' 
      AND TblTasks.TaskId=@TaskId) 
     or 
      (SELECT UserId FROM TblProjects WHERE ProjectId=@ProjectId)
)

Solution

  • I think you are trying to use TblProjectResources.UserId IN trice with OR try like this:

    AND (TblProjectResources.UserId IN
             (SELECT UserId 
             FROM TblProjectResources 
             WHERE IsRemoved=0 
             AND ProjectApproval=1 
             AND ProjectId=@ProjectId) 
         OR
         TblProjectResources.UserId IN 
             (SELECT TblAssignments.AssigneeId 
             FROM TblTasks,TblAssignments 
             WHERE TblTasks.TaskId=TblAssignments.AssignmentEntityId 
             AND TblAssignments.AssignmentEntity='Task' 
             AND TblTasks.TaskId=@TaskId) 
         OR
         TblProjectResources.UserId IN
          (SELECT UserId FROM TblProjects WHERE ProjectId=@ProjectId)
    )