CODEWITHSUNDEEP
mysql
obsessiveCookie
obsessiveCookie

Reputation: 1138

MYSQL return results where there exist at least one from the other table

Say I have these data from two table:

Student Table columns:

id | name

Course Table columns:

id | code | name

and I want to use the Student.id AS Student and Course.id AS Course to get the following:

Student | Course
-----------------  
1       |   C
1       |   B
1       |   A
2       |   F
2       |   B
2       |   A
3       |   C
3       |   B
3       |   F

How would I query it so it will return only the Students with a Course C and their other Courses like below:

Student | Course
-----------------  
1       |   C
1       |   B
1       |   A
3       |   C
3       |   B
3       |   F

? I have tried : 

SELECT Student.id, Course.code FROM Course 
INNER JOIN Student ON Course.student = Student.id
WHERE Course.code = 'C'

but I got only 

Student | Course
-----------------  
1       |   C
3       |   C

Upvotes: 1

Views: 785

Answers (3)

sashkello
sashkello

Reputation: 17871

Here the first JOIN selects only those students which have course C, and second JOIN gives you all the courses for each of those students.

SELECT st.id, c2.code FROM 
Student st 
JOIN Course c ON c.student = st.id AND c.code = "C"
JOIN Course c2 ON c2.student = st.id

You actually don't even need two tables here, because both student and course is available in the Course table, just JOIN it on itself:

SELECT c2.student, c2.code FROM 
Course c JOIN Course c2 ON c.student = c2.student
WHERE c.course = "C"

Here the WHERE clause leaves student id's which have course C and then you JOIN those to find all their courses.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108510

The most efficient approach to this problem is usually an inline view and a JOIN operation, although there are several ways to get an equivalent result.

SELECT Student.id
     , Course.code 
  FROM ( SELECT c.Student
           FROM Course c
          WHERE c.code = 'C'
          GROUP BY c.Student
       ) o
 JOIN Course
   ON Course.Student = o.Student
 JOIN Student
   ON Student.id = Course.Student

Here, we're using an inline view (aliased as o) to get a list of Student taking course code = 'C'.

(NOTE: the query in my answer is based on your original query. If there's a foreign key definition between Course and Student, and we only need to return the Student.id, we could improve performance by omitting the join to Student, and return Course.Student AS id in place of Student.id in the SELECT list.)

Upvotes: 1

John Woo
John Woo

Reputation: 263933

SELECT  s.id, c.code 
FROM    Course c
        INNER JOIN Student s
            ON c.student = s.id
WHERE   EXISTS
        (
            SELECT  1
            FROM    Course c1
            WHERE   c.student = c1.student
                    AND c1.Course = 'C'
        )

Upvotes: 2

Related Questions