-4

In my MySQL database, I have three tables students, classes, courses.

One class has many students.

One class has many courses.

The courses table has one boolean field active, and a string field name.

So overall relationship is (sorry I am not sure how to better illustrate the relationship if it is not clear) :

students (many_to_one) classes (one_to_many) courses

I have a function in Ruby that accepts an array of strings argument for the course names:

def get_student_names_whose_courses_are(active_course_names)
   # Run a raw SQL query for the result
end

I would like to write a raw SQL query to get the names of students whose courses (via class) matches exactly the passed in argument course names and are active.

For example, if active_course_names holds values ['foo','bar']. Student-A has active courses 'foo','bar','etc', student-B has active courses 'foo' and 'bar'. The raw quesry should only return student-B, even though student-A also has the two courses active, the point is an exact matching.

What I tried is this:

select stu.name
from students stu
inner join classes clz ON clz.id = stu.class_id
inner join courses cour ON cour.class_id = clz.id AND cour.name in (#{active_course_names,join("','")})
where cour.active = true;

But with this, it returns both student-A and student-B for the example above. How to make the query so that it returns students that have exactly the active courses?

My demo exapmle here

Salman A
  • 262,204
  • 82
  • 430
  • 521
Leem
  • 17,220
  • 36
  • 109
  • 159
  • I disagree with the data model, and I think a _student_ should have many classes. – Tim Biegeleisen Nov 19 '19 at 10:17
  • Unfortunately, changing the data model is not possible in my project for complex reasons, so I am seeking for an answer with current data model. – Leem Nov 19 '19 at 10:18
  • See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Nov 19 '19 at 10:18
  • I added a demo example in my post. – Leem Nov 19 '19 at 10:25
  • How can a class have different courses for different students? – Nick Nov 19 '19 at 10:29
  • 1
    @Nick It is just naming. I can rename `class` to `classroom`, really not the point of my question. But thanks. – Leem Nov 19 '19 at 10:30
  • @Leem you can see the answer I deleted. I'm still trying to get my head around this problem but it's probably worth trying the query I posted. – Nick Nov 19 '19 at 10:38
  • Thanks @Nick. I checked your answer, I actually don't get why you count on `course.class_id` – Leem Nov 19 '19 at 10:43
  • In a `left join` where the `cour.name` value is not in the active list `cour.class_id` will be NULL, so won't be included in the count. But the more I think about it the more I'm sure it won't work. If you could provide sample table data it would make it a lot easier to figure out a solution. – Nick Nov 19 '19 at 10:46
  • Hmm...doesn't my linked demo sample already contain sample data? – Leem Nov 19 '19 at 10:47

3 Answers3

2

I hope the following answer would be of help to your problem.

If you assign a rank in the same courses (for instance 'FOO'), then you can keep the courses which are repeated in the classes (rank>1) and apply your list of courses. After creating that, you can perform the join to bring the students' information that you need.

SELECT Temp.name,stud.id,stud.name,stud.class_id
FROM (
  /*Create a view with the courses which are present with another classroom*/
  SELECT T.id, T.name, T.active,T.class_id
  FROM (
    /*Create a view with only the active courses and their rank*/
    SELECT id,name,active,class_id,
      @course_rank := IF(@current_course = name,
      @course_rank + 1, 1) AS course_rank,
      @current_course := name
    FROM courses,(select @current_course :=0,@course_rank :=0)  r
    WHERE active = 1
    ORDER BY name, class_id ASC)T
  /*Create a filter to bring only the classrooms with the same courses and the selected courses*/
  WHERE T.course_rank>1 AND T.name IN ('foo','bar'))Temp
JOIN students stud
ON Temp.class_id=stud.class_id
JoPapou13
  • 753
  • 3
  • 8
0

Try this:

select distinct student from (
select stu.name student, cour.name course, count(cour.name )
from students stu
inner join classes clz ON clz.id = stu.class_id
inner join courses cour ON cour.class_id = clz.id 
where cour.active = true
and cour.name in ('foo','bar')
group by stu.name , cour.name
having count(course) =2) A;

Here is the DEMO

In this DEMO only stu-A needs to be selected. Number 2 marks that student only has this two courses active and not 3 of them. Hope this is it...

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • What does `having count(cour.name) = 1` mean? Especially the magic number `1` – Leem Nov 19 '19 at 11:39
  • Check this demo: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=082558f99ece2aa5bbef6da0cce9bd29 In this demo I have removed `HAVING` clause and you will see that this means that Student B has only 1 active course foo and 1 active course bar. So if he had 2 active courses foo he will not be selected... – VBoka Nov 19 '19 at 11:43
  • Thanks, I checked your sample, but shouldn't the expected result also contains `stu-A`? If you check the data, `stu-A` has class id 1 which has course foo and bar be active only. So, the expected query should return both stu-A and stu-B but your query only returns stu-B – Leem Nov 19 '19 at 11:53
  • But stu-A has foo class active not only one time. If you want to show stu-A regardles to this then I did not understood your question correctly and I will correct my answer... – VBoka Nov 19 '19 at 11:57
  • Sorry for my unclear question description, it doesn't matter foo class is active once or n times as long as it is a course active and its name matches `active_course_names` passed into my function. It would be great if you could update your answer. Thanks. – Leem Nov 19 '19 at 12:01
  • Hi @Leem, and ? Any feedback ? – VBoka Nov 20 '19 at 07:21
0

You can use group_concat like this :

SELECT 
     stu.name, 
     GROUP_CONCAT(cour.name ORDER BY cour.name ASC) AS courses
FROM students stu
inner join classes clz ON clz.id = stu.class_id
inner join courses cour ON cour.class_id = clz.id
where 
     cour.active = true
GROUP BY stu.name
HAVING GROUP_CONCAT(cour.name ORDER BY cour.name ASC) = 'bar,foo'

DEMO IS HERE

Gosfly
  • 1,240
  • 1
  • 8
  • 14