CODEWITHSUNDEEP
sqlmysql
Shiro
Shiro

Reputation: 7554

sql select with exact outcome

Asking a simple question, just want everyone have fun to solve it. I got 2 tables. 1. Student 2. Course

Student

+----+--------+
| id | name   |
+----+--------+
|  1 | User1  |
|  2 | User2  |
+----+--------+

Course

+----+------------+------------+
| id | student_id | course_name|
+----+------------+------------+
|  1 |          1 | English    |
|  2 |          1 | Chinese    |
|  3 |          2 | English    |
|  4 |          2 | Japanese   |
+----+------------+------------+

I would like to get the result all student, who have taken English and Chinese, NOT English or Chinese.

Expected result:

    +----+------------+------------+
    | id | student_id | course_name|
    +----+------------+------------+
    |  1 |          1 | English    |
    |  2 |          1 | Chinese    |
    +----+------------+------------+

What we normally do is 

select * from student join course on (student.id = course.student_id) WHERE course_name = 'English' OR course_name = 'Chinese'

but in this result I can get User2 record which is not my expected result. I want the record only display the User take the course English+Chinese only.

Upvotes: 0

Views: 176

Answers (4)

araqnid
araqnid

Reputation: 133662

Just join onto course twice: once for English, once for Chinese. That is:

select student.*
from student
     join course english_course on student.id = english_course.student_id
     join course chinese_course on student.id = chinese_course.student_id
where english_course.course_name = 'English'
      and chinese_course.course_name = 'Chinese'

or even

select * from student
where exists (select 1 from course
              where course.course_name = 'English' and course.student_id = student.id)
      and exists (select 1 from course
                  where course.course_name = 'Chinese' and course.student_id = student.id);

which will also eliminate duplicate (student_id,course_name) entries from course.

I'm assuming (student_id,course_name) is indexed to drive both these. Your naming is a bit odd: the "course" table doesn't describe a course, it describes the association from a student to a course. Personally, I'd call it "student_course" (or similar, maybe suffix "_map" or "_link") and have it contain a "course_id" referencing a course table with an id and name.

(I also prefer to have primary keys named consistently rather than calling them "id" in their own table, but that's just being picky, and much more subjective)

Just for fun:

select student.*
from student
     join course on student.id = course.student_id
where course.course_name = 'English'
intersect
select student.*
from student
     join course on student.id = course.student_id
where course.course_name = 'Chinese'

The reality is that using "intersect" to compare two result sets based on the same tables is a bit silly.

Upvotes: 3

ivans
ivans

Reputation: 1485

You would probably want to do a self-join:

SELECT left.student_id sid1, right.student_id sid2,
       left.course_name cn1, right.course_name cn2
FROM   course left, course right
WHERE  sid1 = sid2 AND
       cn1 = 'English' AND cn2 = 'Chinese'

Joining that to the student table shouldn't be difficult.

Upvotes: 0

Ed.
Ed.

Reputation: 1952

You can use the IN operator

select * from student join course on (student.id = course.student_id)
WHERE course_name = 'English' and student.id IN
(select student.id from student join course on (student.id = course.student_id)
 WHERE course_name = 'Chinese')

Upvotes: 0

Andomar
Andomar

Reputation: 238176

You can use a having clause to enforce to matches:

select  s.student_id
from    student s
join    course c 
on      s.id = c.student_id
        and c.course_name in ('English', 'Chinese')
group by
        s.student_id
having  count(distinct c.course_name) = 2

To retrieve other columns, you could join on this query:

select  *
from    student s
join    course c 
on      s.id = c.student_id
join    (
        <query from above here>
        ) filter on s.id = filter.student_id

Upvotes: 1

Related Questions