Getting single records back from joined tables that may produce multiple records

Question

I've got a student table and an enrollment table; a student could have multiple enrollment records that can be active or inactive.

I want to get a select that has a single student record and an indicator as to whether that student has active enrollments.

I thought about doing this in an inline UDF that uses the student ID in a join to the enrollment table, but I wonder if there's a better way to do it in a single select statement.

The UDF call might look something like:

Select Student_Name,Student_Email,isEnrolled(Student_ID) from Student

What might the alternative - with one SQL statement - look like?

Answer 1

Try someting like this:

SELECT Student_Name, Student_Email, CAST((SELECT TOP 1 1 FROM Enrollments e WHERE e.student_id=s.student_id) as bit) as enrolled FROM Student s

I think you can also use the exists statement in the select but not positive

Answer 2

select  Student_Name,
        Student_Email,
        (select count(*) 
         from Enrollment e 
         where e.student_id = s.student_id
        ) Number_Of_Enrollments 
 from Student e

will get the number of enrollments, which should help.

Answer 3

Why not join to a secondary select? Unlike other solutions this isn't firing a subquery for every row returned, but gathers the enrollment data for everyone all at once. The syntax may not be quite correct, but you should get the idea.

SELECT
    s.student_name,
    s.student_email,
    IsNull( e.enrollment_count, 0 )
FROM
    Students s
LEFT OUTER JOIN (
        SELECT
            student_id,
            count(*) as enrollment_count
        FROM
            enrollments
        WHERE
            active = 1
        GROUP BY
            student_id
    ) e
ON s.student_id = e.student_id

The select from enrollments could also be redone as a function which returns a table for you to join on.

CREATE FUNCTION getAllEnrollmentsGroupedByStudent()
RETURNS @enrollments TABLE
(
    student_id       int,
    enrollment_count int
) AS BEGIN
    INSERT INTO
        @enrollments
    (
        student_id,
        enrollment_count
    ) SELECT
        student_id,
        count(*) as enrollment_count
    FROM
        enrollments
    WHERE
        active = 1
    GROUP BY
        student_id

    RETURN
END


SELECT
    s.student_name,
    s.student_email,
    e.enrollment_count
FROM
    Students s
JOIN 
    dbo.getAllEnrollmentsGroupedByStudent() e
ON  s.student_id = e.student_id

Edit:
Renze de Waal corrected my bad SQL!

Answer 4

try to avoid using udfs or subqueries, they are performance killers. banjolity seems to havea good solution otherwise because it uses a derivd table instead of a UDF or subselect.

Answer 5

  select students.name, 
decode(count(1), 0, "no enrollments", "has enrollments")
     from students, enrollments 
     where 
       students.id = enrollments.sutdent_id and 
       enrollments.is_active = 1 group by students.name

Of course, replace the decode with a function your database uses (or, a case statement).