views:

231

answers:

5

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?

A: 

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

Jonathan
+1  A: 
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.

Renze de Waal
If you had 1000 students this statement is like running 1001 queries since it queries each student's enrollment count separately.
banjollity
No, it's not that bad. The database can optimise, especially if student_id is the first part of an index on enrollment (which I think it will be). The subquery will only need to perform an index range scan on the index of enrollment (the table itself is not needed).
Renze de Waal
+1  A: 

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!

banjollity
Note that if a student does not have enrollments, the join would leave those students out. A left join would help, but then enrollment_count can be null.
Renze de Waal
Good spot. I've made a few edits.
banjollity
A: 

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.

HLGEM
A: 
  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).

david a.