CODEWITHSUNDEEP
sql
Hiep
Hiep

Reputation: 107

why results of two queries are different?

select distinct ID, title, takes.course_id
from course join takes 
    on course.course_id = takes.course_id
where takes.course_id in 
(select takes.course_id
from takes
where ID = '10204');

select ID, title, takes.course_id
from course join takes 
    on course.course_id = takes.course_id
where ID = '10204';

I want to query the course IDs and the titles of the courses that a student whose ID is 10204 takes. The first gives a result with 5000 rows which is incorrect. The second give a correct result. So what is wrong with the first?

Upvotes: 0

Views: 65

Answers (6)

Hiep
Hiep

Reputation: 107

Thanks you guys. I think my problem is that I did not realize that other students can take the same courses with the student having ID 10204. That this why though the condition is to query only courses take by the students 10204, the results is all about the courses taken by both 10204 and other students.

Upvotes: 0

JNevill
JNevill

Reputation: 50034

Adding to the pile on since everyone seems to be offering bits and pieces, some of which are oddly irate...

The first query says "Give me information on the students and courses where the courses were also taken by student 10204"

The second query says "Give me information on the students and courses taken by student 10204"

You say you wanted to get the course IDs and Titles for the courses taken by the student 10204, so obviously the second query is the correct one. You don't care about other student's that have taken the same courses.

Perhaps, to put it into perspective, rewriting the first, and incorrect query will help:

select distinct ID, title, takes.course_id
from course 
    join takes 
        on course.course_id = takes.course_id
    join takes as takes2
        on takes.course_id = takes2.course_id
WHERE
    takes2.ID = '10204');

Upvotes: 1

Michael Doyle
Michael Doyle

Reputation: 427

Essentially the first query can be read as "Find all courses and the students that take them, for any course that is also taken by student 10204". You can look at the first query as a 3 way join. The results of the subquery select takes.course_id from takes where ID = '10204' would be the "third" table.

Upvotes: 1

Thilo
Thilo

Reputation: 262494

The first query gives you data for all students that happen to take a course that 10204 also takes.

Upvotes: 1

deviloper
deviloper

Reputation: 7240

Well that is could be because in the first query you are quering where the course_id in takes table is equal to a specific course_id in that table (WHICH CAN BE NOT UNIQUE)

and in the second query you are straightly querying where the course_id is equal to a unique ID in that table!

Upvotes: 0

jewelnguyen8
jewelnguyen8

Reputation: 249

Because takes.ID != course.ID. The first you use takes.ID in the where clause but the second you use course.ID

Upvotes: -1

Related Questions