CODEWITHSUNDEEP
mysqljoin
Sallar
Sallar

Reputation: 730

Find active and inactive students with MySQL query

I have a list of students in students table, and a list of their courses in students_courses table, and each of these courses may be active or inactive.
I'm wondering if there is a quick way to find "active" users, which means they have at least 1 active course (sc.active = 1) and also inactive users. inactive users are those who don't have a course (there is not a row) or all of their courses are active = 0

also I want to get the row count before getting the actual list.
Thanks in advance

Upvotes: 0

Views: 1716

Answers (2)

Sandeep Rajoria
Sandeep Rajoria

Reputation: 1239

SELECT sum(students_courses.active) as sum, student_id FROM 
    students JOIN students_courses 
    ON students.student_id =  students_courses.student_id
    GROUP BY students_courses.student_id

if sum = 0 this means no courses and is its greater than 0 means some courses

for inactive users get all the student_id which are not in student_course

SELECT student_id FROM students WHERE student_id NOT IN 
     (SELECT DISTINCTROW student_id FROM students)

Upvotes: 1

mistalee
mistalee

Reputation: 851

This query might be a little slow since it uses subqueries, but assuming you have a field studentId you might use this:

SELECT
   students.*,
   IF (
       studentId IN (
           SELECT
               studentId
           FROM
               students_courses
           WHERE
               active=1
   ),1,0) AS hasCourses
FROM
    students

Upvotes: 1

Related Questions