Fewest grouped by distinct - SQL

Ok, I think the answer of this is somewhere but I can't find it... (and even my title is bad)

To be short, I want to get the fewest number of group I can make from a part of an association table

1st, Keep in mind this is already a result of a 5 table (+1k line) join with filter and grouping, that I'll have to run many time on a prod server as powerful as a banana...

2nd, This is a fake case that picture you my problem

After some Querying, I've got this data result :

+--------------------+
|id_course|id_teacher|
+--------------------+
|    6    |    1     |
|    6    |    4     |
|    6    |    14    |
|   33    |    1     |
|   33    |    4     |
|   34    |    1     |
|   34    |    4     |
|   34    |    10    |
+--------------------+

As you can see, I've got 3 courses, witch are teach by up to 3 teacher. I need to attend at one of every course, but I want as few different teacher as possible (I'm shy...).

My first query

Should answer : what is the smallest number of teacher I need to cover every unique course ?

With this data, it's a 1, cause Teacher 1 or Teacher 4 make courses for these 3 one.

Second query

Now that I've already get these courses, I want to go to two other courses, the 32 and the 50, with this schedule :

+--------------------+
|id_course|id_teacher|
+--------------------+
|   32    |    1     |
|   32    |    12    |
|   50    |    12    |
+--------------------+

My question is : For id_course N, will I have to get one more teacher ?

I want to check course by course, so "check for course 32", no need to check many at the same time

The best way I think is to count an inner join with a list of teacher of same fewest rank from the first query, so with our data we got only two : Teacher(1, 4).

For the Course 32, Teacher2 don't do this one, but as the Teacher1 do Courses(6, 33, 34, 32) I don't have to get another teacher.

For the Course 50, the only teacher to do it is the Teacher12, so I'll not find a match in my choice of teacher, and I'll have to get one more (so two in total with these data)

Here is a base [SQLFiddle

Best regards, Blag

Solution

So I've finally found a way to do what I want !

For the first query, as my underlying real need was "is there a single teacher to do everything", I've lower a bit my expectation and go for this one (58 lines on my true case u_u") :

SELECT
    (
        SELECT count(s.id_teacher) nb
        FROM t AS m
        INNER JOIN t AS s
            ON m.id_teacher = s.id_teacher
        GROUP BY m.id_course, m.id_teacher
        ORDER BY nb DESC
        LIMIT 1
        ) AS nbMaxBySingleTeacher,
    (
        SELECT COUNT(DISTINCT id_course) nb
        FROM t
        ) AS nbTotalCourseToDo

[SQLFiddle

And I get back two value that answer my question "is one teacher enough ?"

+--------------------------------------+
|nbMaxBySingleTeacher|nbTotalCourseToDo|
+--------------------------------------+
|         4          |        5        |
+--------------------------------------+

The 2nd query use the schedule of new course, and take the id of one I want to check. It should tell me if I need to get one more teacher, or if it's ok with my actual(s) one.

SELECT COUNT(*) nb
FROM (
    SELECT
        z.id_teacher
    FROM z
    WHERE
        z.id_course = 50
    ) t1
WHERE
    FIND_IN_SET(t1.id_teacher, (
        SELECT GROUP_CONCAT(t2.id_teacher) lst
        FROM (
            SELECT DISTINCT COUNT(s.id_teacher) nb, m.id_teacher
            FROM t AS m
            INNER JOIN t AS s
                ON m.id_teacher = s.id_teacher
            GROUP BY m.id_course, m.id_teacher
            ORDER BY nb DESC
            ) t2
        GROUP BY t2.nb
        ORDER BY nb DESC
        LIMIT 1
        ));

[SQLFiddle

This tell me the number of teacher that are able to teach the courses I already have AND the new one I want. So if it's over zero, then I don't need a new teacher :

+--+
|nb|
+--+
|1 |
+--+