What is the wrong with this SQL Query?

Question

I am developing a web application and now I need to make this application able to generate some reports from its database. I have a huge database with a quite complicated structure. I came up with a large SQL Query and it was working fine with me until I changed a little bit the structure of the database. I modified it according to these changes but still it does not work. I tested it in the SQLServer Management Studio and I failed. Sometimes it tells me that the error is with syntax. Sometimes it tells me that the error with ''.

Simple structure of the database:

employee table: Name, Username, JobTitle, BadgeNo, EmpOrgType, DivisionID

Departments table: DepartmentCode, DepartmentName

courses table: CourseID, CourseName, GroupID

group table: GroupID, GroupName

employee_courses: employeeID, courseID

The first attribute in each table is the primary key of each table except for the last table, employee_courses table.

The query is:

select *
from
    (SELECT TOP (100) PERCENT 
            dbo.Divisions.DivisionName, 
            dbo.employee.EmpOrgType, 
            dbo.employee.Name, 
            T1.Username, 
            courses_2.CourseName, 
            CASE WHEN dbo.employee_courses.courseId IS NULL THEN '' '' 
                 ELSE ''Yes'' END AS CourseId
     FROM dbo.employee_courses 
     RIGHT OUTER JOIN dbo.courses AS courses_2 
     INNER JOIN (SELECT employee_1.Username, 
                        courses_1.CourseID
                 FROM dbo.employee AS employee_1 
                 CROSS JOIN dbo.courses AS courses_1) 
                                  AS T1 ON courses_2.CourseID = T1.CourseID 
     INNER JOIN dbo.employee ON T1.Username = dbo.employee.Username 
                             ON dbo.employee_courses.employeeId = T1.Username AND
                                dbo.employee_courses.courseId = T1.CourseID
     ORDER BY T1.Username
    ) DataTable

This query should retrieve the information of the employee: Name, Username, JobTitle, BadgeNo, EmpOrgType and DivisionName. Then, it should display the courses that he took them from the courses table with mentioning the GroupName of those taken courses.

Answer 1

Do you actually want to see the single quotes in your result string? If yes, try:

SELECT *
FROM   (SELECT TOP (100) PERCENT dbo.divisions.divisionname,
                             dbo.employee.emporgtype,
                             dbo.employee.name,
                             t1.username,
                             courses_2.coursename,
                             CASE
                               WHEN dbo.employee_courses.courseid IS NULL
                             THEN ''' '''
                               ELSE '''Yes'''
                             END AS courseid
    FROM   dbo.employee_courses
           RIGHT OUTER JOIN dbo.courses AS courses_2
                            INNER JOIN (SELECT employee_1.username,
                                               courses_1.courseid
                                        FROM   dbo.employee AS employee_1
                                               CROSS JOIN dbo.courses AS
                                                          courses_1)
                                       AS t1
                              ON courses_2.courseid = t1.courseid
                            INNER JOIN dbo.employee
                              ON t1.username = dbo.employee.username
             ON dbo.employee_courses.employeeid = t1.username
                AND dbo.employee_courses.courseid = t1.courseid
    ORDER  BY t1.username) datatable  

Answer 2

Sometimes it tells me that the error is with syntax. Sometimes it tells me that the error with ''.

The problem is likely here

CASE WHEN dbo.employee_courses.courseId IS NULL THEN '' '' ELSE ''Yes'' END AS CourseId