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

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?

From stackoverflow

• 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

• 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.

  banjollity : If you had 1000 students this statement is like running 1001 queries since it queries each student's enrollment count separately.

  Renze de Waal : 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).

• 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!

  Renze de Waal : 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.

  banjollity : Good spot. I've made a few edits.

• 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.

•   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).