Inner select syntax error in sql where clause

Question

I have two tables.

• TblA has columns: StudentName, StudentID, FeeRateID, CourseType, CourseSpeciality.

  StudentGender.CourseType has 3 categories (Computer Science, Social Studies and Literary Studies). CourseSpeciality (under Computer Science) has 4 different categories (Java, Python, SQL, R).Social studies has 2 and Literary studies has 2 CourseSpecialities.

• Table TbLB has StudentID, DateOfRegistration, FeeRateID.

I need to join the two tables to get StudentName, FeeRate, FeeRateID for administrative purpose. I am told to join two tables to obtain the details for all students. Literary and Social Studies students’ all CourseSpecialty has to be included.

But I am asked to include Computer Science students with CourseSpecialty of Java, Python and R.

I tried this, but my code throws a syntax error. I know problem lies in the inner select (Where clause) but I'm not able to fix this on my own. Can someone help me please?

SELECT
    A.StudentName, A.StudentID, A.CourseType, B.FeeRateID
FROM
    A
LEFT JOIN
    B ON a.FeeRateId = b.FeeRateID
WHERE 
    A.CourseType IN ('Social Studies', 'Literary Studies', 'Computer Science' (SELECT CourseType FROM A WHERE CourseType = 'Computer Science' and CourseSpecialty IN ('Java', 'Python', R);

Answer 1

Don't understand why is the subquery needed in this case at all, since it will return 'Computer Science' which is already specified in the IN(...).

SELECT
    A.StudentName, A.StudentID, A.CourseType, B.FeeRateID
FROM A
    LEFT JOIN B ON a.FeeRateId = b.FeeRateID
WHERE 
    A.CourseType IN ('Social Studies', 'Literary Studies') 
    OR 
    (A.CourseType = 'Computer Science' AND A.CourseSpecialty IN ('Java', 'Python', 'R'))