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

Question

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 1

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

Answer 2

SELECT  cl.class_id, cl.class_name, c.course_id, c.room
FROM    classes cl
JOIN    courses c
ON      c.course_id = 
        (
        SELECT  ci.course_id
        FROM    courses ci
        WHERE   ci.class_id = cl.class_id
                AND ci.room = 1
        ORDER BY
                ci.class_id, ci.course_id -- or whatever
        LIMIT 1
        )

By changing the ORDER BY clause in the subquery, you can define which of the classes will be returned (that with the least or greatest id etc)