T-SQL error with subquery

Question

I need help with a T-SQL select statement. It is for SQL Server 2014

I have 3 tables.

• Table 1 (Courses) has 2 columns that I need to query on - AllowReEnrollment and ReEnrollAutomatically

• Table 2 (CourseEnrollments) has a CourseId column that is the foreign key to the Id of the Courses table. Courses can have multiple enrollments.

• Table 3 (Jobs) has a column called Arguments which holds Json data with one of the values being EnrollmentId {"EnrollmentId":{ENROLLMENT_ID_GUID}} which is the Id from the CourseEnrollments table

I need to check the Jobs table to find out if there are any jobs scheduled for a CourseEnrollment where its Course has had the AllowReEnroll flag turned off but the ReEnrollAutomatically flag is on.

This is what I have been trying

SELECT
    *
FROM
    Jobs
WHERE
    Arguments LIKE '%' + (
        SELECT
            CONVERT( nvarchar(50), Id )
        FROM
            CourseEnrollments
        WHERE
            CourseId IN (
                SELECT
                    Id
                FROM
                    Courses
                WHERE
                    AllowReEnroll = 0
                    AND
                    ReEnrollAutomatically = 1
            )
    ) +'%'

I can query the CourseEnrollments table to get all of the ids of CourseEnrollments where the Course's flags are set accordingly with

SELECT
    *
FROM
    CourseEnrollments
WHERE
    CourseId IN (
        SELECT
            Id
        FROM
            Courses
        WHERE
            AllowReEnroll = 0
            AND
            ReEnrollAutomatically = 1
    )

but when I try to use that as a subquery I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'

Not being a dba I have spent the day trying to figure this out. How do I write this query?

Answer 1

Use EXISTS/Join to do this

Using EXISTS

SELECT *
FROM   Jobs j
WHERE  EXISTS (SELECT 1
               FROM   CourseEnrollments c
               WHERE  c.CourseId IN (SELECT Id
                                     FROM   Courses
                                     WHERE  AllowReEnroll = 0
                                            AND ReEnrollAutomatically = 1)
                      AND j.Arguments LIKE '%' + CONVERT(NVARCHAR(50), c.Id) + '%') 

Using INNER JOIN

SELECT j.*
FROM   Jobs j
       JOIN CourseEnrollments c
         ON j.Arguments LIKE '%' + CONVERT(NVARCHAR(50), c.Id) + '%'
WHERE  c.CourseId IN (SELECT Id
                      FROM   Courses
                      WHERE  AllowReEnroll = 0
                             AND ReEnrollAutomatically = 1)