Error when Joining table [closed]

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

---

开发者_Go百科

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 9 years ago.

Improve this question

I am getting the error, Incorrect syntax near the keyword 'WHERE'

All i want to do is use some columns from another table

SELECT  unitCode, studentID, s.studentFName 
FROM    Student As S Right outer Join       
      (SELECT unitCode, studentID, 
          SUM(CASE WHEN subStatus = 'Yes' THEN 1 ELSE 0 END) AS CountYes, 
          SUM(CASE WHEN subStatus = 'No' THEN 1 ELSE 0 END) AS CountNo
       FROM Assignment 
       GROUP BY unitCode, studentID) 
WHERE (CountNo > 0) AND (CountYes = 0) AND s.studentID = assignment.studentID 

---

Your join is missing its ON clause and the subselect is missing the alias:

SELECT
    unitCode, studentID, s.studentFName 
FROM
    Student As S
    Right outer Join       
    (
        SELECT
            unitCode, studentID, 
            SUM(CASE WHEN subStatus = 'Yes' THEN 1 ELSE 0 END) AS CountYes, 
            SUM(CASE WHEN subStatus = 'No' THEN 1 ELSE 0 END) AS CountNo
        FROM
            Assignment 
        GROUP BY unitCode, studentID
    ) as assignment 
    on s.studentID = assignment.studentID 
WHERE 
    (CountNo > 0) AND (CountYes = 0)
;

---

Subselects must have an alias in SQL Server.

Change this:

OUTER JOIN (SELECT ...)

to this:

OUTER JOIN (SELECT ...) T1