Ben Saufley Ben Saufley - 1 month ago 9
MySQL Question

Can I JOIN two tables with only one match for each row of one table in MySQL?

I've got one table, Classes, and another table that I call "courses" - that is, instances of a class taught by a specific person in a specific place. I want to pull a table that basically just shows me which Classes are active based on certain course parameters. For example:


CLASSES
class_id|class_name
--------|------------
1|Class One
2|Class Two
3|Different Class
etc...

COURSES
course_id|class_id|room
---------|--------|--------
1| 3| 1
2| 3| 2
3| 1| 1
4| 3| 1
5| 3| 2
6| 2| 1
etc...


I'm wondering if there's a way that I can just get something like
SELECT classes.* FROM classes JOIN courses ON classes.class_id=courses.class_id WHERE courses.room=1
to return only one instance of each class. What's happening is that I'm getting only the classes that take place in room 1, but I'm getting multiple instances of each because there are multiple instances of that class in the course table in room 1.

I've tried all different sorts of
JOIN
- left, right, inner, etc. - and because I'm pulling from one table based on specifications from the other, they all appear to give me the same result.

So I'm getting:


class_id| class_name |course_id|room
--------|--------------|---------|--------
1|Class One | 3| 1
2|Class Two | 6| 1
3|Diferent Class| 1| 1
3|Diferent Class| 4| 1
etc...


But I want to just get:


class_id| class_name |course_id|room
--------|--------------|---------|--------
1|Class One | 3| 1
2|Class Two | 6| 1
3|Diferent Class| 1| 1
etc...


Can I have it only
JOIN
on the first match for each row in Classes? I'm kind of new with MySQL so I'm having a little trouble expressing what I want to do clearly; I apologize.

Also: I'm pulling all of this into PHP via
PDO
- maybe there's a specific way to accomplish this in
PDO
?

Answer
SELECT
   classes.class_id,
   classes.name,
   courses.room
FROM classes
   JOIN courses
      ON classes.class_id=courses.class_id
WHERE courses.room=1
GROUP BY classes.class_id,classes.name,courses.room

GROUP BY allows you to aggregate results on the fields specified, so (in this instance) it will take just the unique tuple of (classes.class_id,classes.name,courses.room)

for more details http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html