如果关联表的列中有一个不满足条件，如何忽略表中的一行?

I am trying to do the following in ruby on rails, but even if I can get an answer in mysql that would be great.

我正在尝试在ruby on rails中做下面的事情，但是即使我能在mysql中得到答案，那也太棒了。

I have tables Student and Courses. Student -> Courses is one-to-many relationship.

我有桌子、学生和课程。学生->课程是一对多的关系。

  Student                   Course
|--------------|   |--------------------------------------| 
|id| name      |   |id | course_name | grade | student_id | 
|----------    |   |--------------------------------------| 
|S1| student 1 |   |C1 | Course1     | A     | S1         |
|S2| student 2 |   |C2 | Course2     | C     | S1         |
|----------    |   |C3 | Course1     | A     | S2         |
                   |C4 | Course2     | B     | S2         |
                   |--------------------------------------|

select * from Student 
where id NOT IN (select student_id from Course where grade = 'C')

I want to achieve same result using single SQL JOIN statement or using activerecord.

我希望使用单个SQL JOIN语句或使用activerecord实现相同的结果。

2 个解决方案

#1

SELECT * FROM Student s 
LEFT JOIN Course c ON s.id=c.student_id AND c.grade = 'C' 
WHERE c.student_id IS NULL;

Or join to your subquery

或连接到子查询

SELECT * FROM Student s 
LEFT JOIN (SELECT student_id FROM Course WHERE grade = 'C') c
WHERE c.student_id IS NULL;

Or use exists

或者使用存在

SELECT * FROM Student s 
WHERE NOT EXISTS (SELECT NULL FROM Course WHERE grade = 'C' AND student_id=s.id);

I'm afraid I can't test this at the moment and I have a suspicion that these queries may not help you. I have no experience with ActiveRecord. Let me know in the comments.

恐怕我现在不能测试这个，我怀疑这些查询可能对你没有帮助。我没有使用ActiveRecord的经验。请在评论中告诉我。

#2

oracle:

select distinct s.*  from student s left join course c on c.student_id=s.id where c.grade!='c'

Student.all(:select=>"distinct student.*",:joins=>"left join course on course.student_id=student.id",:conditions=>"course.grade!='c'")

#1

SELECT * FROM Student s 
LEFT JOIN Course c ON s.id=c.student_id AND c.grade = 'C' 
WHERE c.student_id IS NULL;