CODEWITHSUNDEEP
mysql
user2005449
user2005449

Reputation: 23

How do I use join to get a single result searching multiple rows on the join table?

I don't really know how to word this but I think an example would make this really easy. Lets say there is a database of students and classes. 

Students:
id | name
1  | Bill
2  | Tom

Classes
id | name
1  | history
2  | math
3  | science

student_class
student_id | class_id
1          | 1
1          | 3

What I'm trying to do is get the query to return student Bill ONLY IF I search for classes 1 AND 3, right now I can get it to return Bill if I search for 1, 1 and 3, and 1, 2, and 3 using a normal left join. I'm trying to search from the students table and find results that only include all search terms listed. Something like:

SELECT * FROM students
LEFT JOIN studen_classes on students.id = student_classes.student_id
WHERE student_classes.class_id IN (1,3)

I've also tried 

WHERE student_classes.class_id = 1 AND student_classes.class_id = 3

They both will return results if a student is attached to class 1 OR 3 when I want the results to only return students who have class 1 AND 3. Any ideas?

Upvotes: 0

Views: 27

Answers (1)

Marc B
Marc B

Reputation: 360902

Put a HAVING clause in there, with appropriate GROUP BY and whatnot:

HAVING COUNT(student_class.class_id) = 2

That'll post-filter the query results to allow only those records where the count of class ID's is 2, meaning that they're in both the 1 and 3 classes. If they're in only one of the two, or none, their count would be 0 or 1.

Upvotes: 1

Related Questions